- Repository Keyring
- Install PostgreSQL
- User Database
- Socket Connection
- Migrating Backend
- Backup and Restore
- Install GnuPG from apt, and get postgres-common package which contains a shell script for importing the postgresql repository keyring:
root@host:~# apt-get install gnupg2 && \
wget http://deb.debian.org/debian/pool/main/p/postgresql-common/postgresql-common_225+deb11u1.tar.xz && \
tar -xf postgresql-common_225\+deb11u1.tar.xz && rm postgresql-common_225\+deb11u1.tar.xz && \
mkdir -p /usr/share/postgresql-common/pgdg && \
mv ./postgresql-common/pgdg/apt.postgresql.org.sh /usr/share/postgresql-common/pgdg/ && \
rm -r ./postgresql-common && \
/usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
- Add Packages
root@host:~# apt-get install postgresql-15 postgresql-server-dev-15
- Change the postgres user password
root@host:~# passwd postgres
- Increase shared memory buffer
nano /etc/postgresql/15/main/postgresql.conf
shared_buffers = 8192MB # min 128kB
- Verify the service is running, and start it if not
user@host:~$ /etc/init.d/postgresql status
user@host:~$ /etc/init.d/postgresql start
- Log in as postgres user
user@host:~$ su - postgres
- Create database user
postgres@host:~$ createuser --pwprompt psqluser
- Create one or more databases
postgres@host:~$ createdb -O psqluser mapdb && \
createdb -O psqluser playerdb && \
createdb -O psqluser authdb && \
createdb -O psqluser mapserverdb
- Verify you can connect to a database with the user
postgres@host:~$ psql -d mapdb -h localhost -U psqluser
Faster than IP connection for connecting services within localhost
- Edit
/etc/postgresql/15/main/pg_hba.conf
and changepeer
toscram-sha-256
:
# "local" is for Unix domain socket connections only
local all all scram-sha-256
- Edit
/etc/postgresql/15/main/pg_ident.conf
and add:
# MAPNAME SYSTEM-USERNAME PG-USERNAME
local user psqluser
-
SYSTEM-USERNAME
is the username which starts minetest service, ie;user
-
PG-USERNAME
is the database owner name to whichuser
will connect;psqluser
-
MAPNAME
is the map name that was used in pg_hba.conf; set it tolocal
-
Restart the PostgreSQL service
root@host:~# /etc/init.d/postgresql restart
- Create a .pgpass file with the correct permissions
user@host:~$ touch .pgpass && chmod 600 .pgpass && \
echo "localhost:5432:mapdb:psqluser:securepassword
localhost:5432:authdb:psqluser:securepassword
localhost:5432:playerdb:psqluser:securepassword" > .pgpass
- It is now possible to connect to the database and the
postgres
user with youruser
account
# Connect to database
user@host:~$ psql -d mapdb -U psqluser
# Change to postgres user
user@host:~$ su - postgres
You now have a running PostgreSQL service.
Next step is Compile Minetest or MultiCraft
Make a backup copy of your files before migrating!
- Edit
world.mt
add the pgslq_connection of the backend to be migrated. - Do not change the backend type, it will be updated by the migration automation.
# Socket connections:
backend = sqlite3
pgsql_connection = postgresql:///mapdb?host=/var/run/postgresql&user=psqluser&password=securepassword&dbname=mapdb
auth_backend = sqlite3
pgsql_auth_connection = postgresql:///mapdb?host=/var/run/postgresql&user=psqluser&password=securepassword&dbname=mapdb
player_backend = sqlite3
pgsql_player_connection = postgresql:///mapdb?host=/var/run/postgresql&user=psqluser&password=securepassword&dbname=mapdb
# IP Connections
# pgsql_connection = host=127.0.0.1 port=5432 user=psqluser password=securepassword dbname=mapdb
# pgsql_auth_connection = host=127.0.0.1 port=5432 user=psqluser password=securepassword dbname=mapdb
# pgsql_player_connection = host=127.0.0.1 port=5432 user=psqluser password=securepassword dbname=mapdb
- Run the minetestserver binary with migration flags
user@host:~$ ./minetest-master/bin/minetestserver --migrate postgresql --world /home/user/minetest-master/worlds/world && \
./minetest-master/bin/minetestserver --migrate-auth postgresql --world /home/user/minetest-master/worlds/world && \
./minetest-master/bin/minetestserver --migrate-players postgresql --world /home/user/minetest-master/worlds/world
Depending on the size of your map file, migration may take several hours.
-
- Dump databases
user@host:~$ pg_dump -U psqluser -h localhost -d mapdb --clean -O | gzip -c > dump_mapdb.gz && \
pg_dump -U psqluser -h localhost -d authdb --clean -O | gzip -c > dump_mapdb.gz && \
pg_dump -U psqluser -h localhost -d playerdb --clean -O | gzip -c > dump_mapdb.gz
- Dump and compress local database to remote server
user@host:~$ pg_dump -U psqluser -h localhost -d mapdb --clean -O | ssh -p 7743 user@remote -T "gzip -c > mapdb.gz"
- Dump all databases into single file:
user@host:~$ su - postgres bash -c 'pg_dumpall --clean -O' | gzip -c > dumpall_db.gz
- Restore from dump file to existing database
user@host:~$ gunzip -k -c mapdb.gz | psql -U mapdb -h localhost -d mapdb
https://www.postgresql.org/docs/current/backup-dump.html
CC0 1.0 Universal
No Copyright
The person who associated a work with this deed has dedicated the work to the public domain by waiving all of his or her rights to the work worldwide under copyright law, including all related and neighboring rights, to the extent allowed by law.
You can copy, modify, distribute and perform the work, even for commercial purposes, all without asking permission.
In no way are the patent or trademark rights of any person affected by CC0, nor are the rights that other persons may have in the work or in how the work is used, such as publicity or privacy rights.
Unless expressly stated otherwise, the person who associated a work with this deed makes no warranties about the work, and disclaims liability for all uses of the work, to the fullest extent permitted by applicable law. When using or citing the work, you should not imply endorsement by the author or the affirmer.