I do think that Minetest could have had better and more mature access to PostgreSQL, but the process of submitting a pull request is messy and requires some time and know how with git. In my case, many core devs had ideas for changes to the code. I complied as much as possible, but to a certain extent it the process seems to become almost an argument between devs with my pull request caught in the middle. That might sound like a bad thing, but it's not. That is a byproduct of project growth. At one point the project was small and everyone took ownership of everything, but once a project grows beyond a certain size there has to be some refactoring of what the project is about.
In this case, there was concern about too much SQL and if PostgreSQL would be a server only backend. In the end, it really was representative of a major unaddressed issue at the time: Would MT sacrifice support for large servers to keep the codebase exclusively focused on small clients and desktop servers.
That's not at all an easy question to answer. Focus on the desktop and there could be a fork that might drive some good devs away. The devs are doing great and doing a great job. I have no complaints.
My advice to anyone who wants to put out a pull request: Post the alternative code and see if you can talk a code dev into trying it and giving input. Allow the public to give feedback and try it as well. Don't make a pull request. Hand it to an interested code dev and allow them to shepherd the code through the approval process if you can. They deal with it daily and it is a soul-crushingly annoying process otherwise.
Johnny Joy wrote:These instructions are for a new backend that will hopefully be added to Minetest soon.
PostgreSQL Backend
The PostgreSQL backend is not for everyone. It requires some experience with
PostgreSQL or a willingness to learn. This is not something for the casual user.
Status
This is an initial release, with improvements, and enhancements to come. Right
now there is little more than the blocks table, but performance has been good.
In time there will be mods that will make more use of the database, rather than
flat files.
Test Drive
Download from git, compile, and run. Please post your experiences and ideas.
https://github.com/johnnyjoy/minetest
Quick Start
Ubuntu 14.04 or greater
TodoCode: Select all
$ sudo adduser minetest $ sudo su postgres $ createuser minetest $ createdb --owner=minetest $ exit $ sudo adduser minetest $ sudo su minetest $ ~minetest $ mkdir .minetest/worlds/<worldname> $ echo "gameid=minetest" > .minetest/worlds/<worldname>/world.mt $ echo "backend=postgresql" >> .minetest/worlds/<worldname>/world.mt $ minetestserver --worldname <worldname>
Advantages
- Upsert support for PostgreSQL 9.5 (done)
- Add backend selection and configuration from UI
- Create db independent interface for rollback
- Add support for minetestmapper
- Adapt mods to use backend dbs rather than flat files
- Windows test & docs
Further Reading
- Greater use of memory to improve performance
- Automatic re-indexing using the auto vacuum feature
- Backups while minetest users are playing
- Continuous backups, using automatic transaction log archiving
- Ability use separate application and database server, for increased performance
- Redundancy, load balancing, and failover, via PostgreSQL features
- Use of triggers and additional columns or tables
How to compile from source
- PostgreSQL: http://www.postgresql.org/
- Continous Archiving: http://www.postgresql.org/docs/9.4/stat ... iving.html
- Tuning: https://wiki.postgresql.org/wiki/Tuning ... SQL_Server
- Kernel Resources: http://www.postgresql.org/docs/9.4/stat ... urces.html
- Auto Vacuum: http://www.postgresql.org/docs/9.4/stat ... acuum.html
- High Availability, Load Balancing, and Replication: http://www.postgresql.org/docs/9.4/stat ... ility.html
I will explain how to compile Minetest with the PostgreSQL backend on Ubuntu
14.10. These settings are minimal and meant to be combined with your normal
biuld configuration.
ConfigurationCode: Select all
# apt-get install libpq-dev postgresql-client postgresql-server-dev # cmake -DENABLE_POSTGRESQL=TRUE # make
The settings for PostgreSQL user, password, database name, and database host
are located in the world.mt file. These are the settings.
See: http://www.postgresql.org/docs/9.4/stat ... nnect.html
Database AuthenticationCode: Select all
backend = postgresql pg_connection_info = connection string, defaults to dbname=minetest
How Minetest authenticates to the database is left up to the user.
See: http://www.postgresql.org/docs/9.4/stat ... thods.html
I would recommend a pgpass password file, which stores the credentials a safe location, and does not require any configuration to use.
See: http://www.postgresql.org/docs/9.4/stat ... gpass.html
Schema
This is all that is required. Table is automatically created if permissions allow.
Getting CreativeCode: Select all
CREATE TABLE IF NOT EXISTS blocks ( x INT NOT NULL, y INT NOT NULL, z INT NOT NULL, data BYTEA NOT NULL, PRIMARY KEY(x, y, z) );
On my server I save the timestamp for when block data was originally generated
and when it was last modified. I also store a copy of the originally generated
block, in the event an area might be to be reverted for some reason, or even
to use those blocks to explore the world in it's original state later on.
NOTE: I know you can explore your world by regenerating it, but this saves
the original blocks, bugs and all.
Additionally triggers can be created to collect statistics for capacity planning.Code: Select all
CREATE TABLE blocks ( x INT NOT NULL, y INT NOT NULL, z INT NOT NULL, data BYTEA NOT NULL, mtime timestamp without time zone DEFAULT now(), ctime timestamp without time zone DEFAULT now(), PRIMARY KEY(x, y, z) ); CREATE TABLE blocks_original ( x INT NOT NULL, y INT NOT NULL, z INT NOT NULL, data BYTEA NOT NULL, ctime timestamp without time zone DEFAULT now(), PRIMARY KEY(x, y, z) ); CREATE FUNCTION blocks_mtime() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN NEW.mtime = now(); RETURN NEW; END; $$; CREATE FUNCTION blocks() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN INSERT INTO blocks_orignal(x, y, z, data) VALUES(NEW.z, NEW.y, NEW.z, NEW.data); RETURN NEW; END; $$; CREATE TRIGGER blocks_mtime BEFORE UPDATE ON blocks FOR EACH ROW EXECUTE PROCEDURE blocks_mtime(); CREATE TRIGGER insert_save_original AFTER INSERT ON blocks FOR EACH ROW EXECUTE PROCEDURE save_original_block();