Johnny Joy wrote:These instructions are for a new backend that will hopefully be added to Minetest soon.
PostgreSQL BackendThe 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.
StatusThis 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 DriveDownload from git, compile, and run. Please post your experiences and ideas.
https://github.com/johnnyjoy/minetestQuick StartUbuntu 14.04 or greaterYour phone or window isn't wide enough to display the code box. If it's a phone, try rotating it to landscape mode.
- Code: 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>
Todo - 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
Advantages- 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
Further ReadingHow to compile from sourceI 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.
Your phone or window isn't wide enough to display the code box. If it's a phone, try rotating it to landscape mode.
- Code: Select all
# apt-get install libpq-dev postgresql-client postgresql-server-dev
# cmake -DENABLE_POSTGRESQL=TRUE
# make
ConfigurationThe 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/static/libpq-connect.htmlYour phone or window isn't wide enough to display the code box. If it's a phone, try rotating it to landscape mode.
- Code: Select all
backend = postgresql
pg_connection_info = connection string, defaults to dbname=minetest
Database AuthenticationHow Minetest authenticates to the database is left up to the user.
See:
http://www.postgresql.org/docs/9.4/static/auth-methods.htmlI 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/static/libpq-pgpass.htmlSchemaThis is all that is required. Table is automatically created if permissions allow.
Your phone or window isn't wide enough to display the code box. If it's a phone, try rotating it to landscape mode.
- Code: 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)
);
Getting CreativeOn 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.
Your phone or window isn't wide enough to display the code box. If it's a phone, try rotating it to landscape mode.
- 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();
Additionally triggers can be created to collect statistics for capacity planning.