minetestserver -migrate and postgresql?
- ExeterDad
- Member
- Posts: 1717
- Joined: Sun Jun 01, 2014 20:00
- In-game: ExeterDad
- Location: New Hampshire U.S.A
minetestserver -migrate and postgresql?
I finally managed to build a .deb package with postgresql support. Cmake bug upstream prevents postgresql detection.
I'm wanting to convert my sqlite map to postgresql with the -migrate option. I've done it without issues in the past with redis, but unsure how to go about it with postgresql. I'm not even sure if migration is supported yet with postgresql. If so, do I need to supply a connection string somewhere? Or would it use some sort of defaults?
Googling Minetest and postgresql yeilds sparse results as it seems very few people are using postgresql as their backend.
Insight would be greatly appreciated. Examples would be awesome as well.
Thanks!
I'm wanting to convert my sqlite map to postgresql with the -migrate option. I've done it without issues in the past with redis, but unsure how to go about it with postgresql. I'm not even sure if migration is supported yet with postgresql. If so, do I need to supply a connection string somewhere? Or would it use some sort of defaults?
Googling Minetest and postgresql yeilds sparse results as it seems very few people are using postgresql as their backend.
Insight would be greatly appreciated. Examples would be awesome as well.
Thanks!
HOMETOWN -Our little server. Keep the HOMETOWN chatter @ http://hometownserver.com - Our server map: http://media.hometownserver.com
- ExeterDad
- Member
- Posts: 1717
- Joined: Sun Jun 01, 2014 20:00
- In-game: ExeterDad
- Location: New Hampshire U.S.A
Re: minetestserver -migrate and postgresql?
So apparently minetestserver does support migrating from sqlite to postgresql. Yeay!!!
After much fiddling with various conversion tools without any luck, I went for it with my local instance of our server. Good thing I did as there was MUCH trial and error while I was figuring it out. I'm going to outline what I did for others (or even myself) to get it working for them. I know nothing about postgresql other than I learned today so feel free to pick this tutorial apart so I may edit this post.
Our server is running Debian Jessie completely Vanilla, with only what is required to build and run minetest server so your results may be quite different than mine.
First off, you need minetestserver with postgresql support intalled. I had already built and rolled minetest in a .deb package for easy install and clean removal. I just need to install it for the first time but not start it yet:
Now install postgresql and client:
This installed:
postgresql-9.4
postgresql-client-9.4
postgresql-client-common
postgresql-common
Check to see if postgresql server is up and running. There will be a bunch of output if this command is run and the server is up:
On a Debian box, user postres is created. In the *nix terminal we need to become that user and run the postgresql client to connect and create a user, password and database for our map.
The prompt will change appearance when you are in the client. Type \q to quit if you change your mind.
I'm now going to create a new database user "minetest" with a password of "12345" and give minetest all privs on a new database called "myworld". Use whatever you like... maybe not such a lame password though.
The postgres=# portion of the above lines is the client "prompt", don't type that part. Also note, the uppercase commands must be uppercase. The semi-colons ";" must not be omitted. If they aren't included, it will be assumed you are typing a extra long command.
Check to make sure the new database is there and owned by new user "minetest". List the databases with \l <--- backslash and a lowercase "L":
Hopefully everthing is good, now exit the client with \q
Now we create a connection string in our worlds world.mt file. Do not change the backend string from sqlite yet. The migration will do that for you when it's done.
So the top part of world.mt should look like this:
Save world.mt and let's start minetestserver and begin the migration. Launch it with the --migrate option, and --world option. I always use the full path to the world directory in quotes when launching via cli. Your path will be different naturally. Mine is in my user directory. So I don't have to deal with permissions when editing stuff.
You should expect to see something like this if all went well:
Now you can start your server with your usual method and it should "just work"
Enjoy!
After much fiddling with various conversion tools without any luck, I went for it with my local instance of our server. Good thing I did as there was MUCH trial and error while I was figuring it out. I'm going to outline what I did for others (or even myself) to get it working for them. I know nothing about postgresql other than I learned today so feel free to pick this tutorial apart so I may edit this post.
Our server is running Debian Jessie completely Vanilla, with only what is required to build and run minetest server so your results may be quite different than mine.
First off, you need minetestserver with postgresql support intalled. I had already built and rolled minetest in a .deb package for easy install and clean removal. I just need to install it for the first time but not start it yet:
Code: Select all
sudo dpkg -i minetest_0.4.15-dev-20170215_amd64.deb
Code: Select all
sudo apt-get install postgresql-9.4
postgresql-9.4
postgresql-client-9.4
postgresql-client-common
postgresql-common
Check to see if postgresql server is up and running. There will be a bunch of output if this command is run and the server is up:
Code: Select all
ps -ef | grep postgre
Code: Select all
sudo -u postgres psql
I'm now going to create a new database user "minetest" with a password of "12345" and give minetest all privs on a new database called "myworld". Use whatever you like... maybe not such a lame password though.
Code: Select all
postgres=# CREATE USER minetest WITH PASSWORD '12345';
postgres=# CREATE DATABASE myworld OWNER minetest;
postgres=# GRANT ALL PRIVILEGES ON DATABASE myworld TO minetest;
Check to make sure the new database is there and owned by new user "minetest". List the databases with \l <--- backslash and a lowercase "L":
Code: Select all
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+---------+-------+-----------------------
myworld | minetest | LATIN1 | en_US | en_US | =Tc/minetest +
| | | | | minetest=CTc/minetest
postgres | postgres | LATIN1 | en_US | en_US |
template0 | postgres | LATIN1 | en_US | en_US | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | LATIN1 | en_US | en_US | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
Now we create a connection string in our worlds world.mt file. Do not change the backend string from sqlite yet. The migration will do that for you when it's done.
So the top part of world.mt should look like this:
Code: Select all
gameid = minetest
backend = sqlite3
pgsql_connection = host=127.0.0.1 port=5432 user=minetest password=12345 dbname=myworld
Code: Select all
minetestserver --migrate postgresql --world "/home/leeh/.minetest/worlds/myworld"
Code: Select all
leeh@s103139:~$ minetestserver --migrate postgresql --world "/home/leeh/.minetest/worlds/myworld"
2017-02-16 21:08:37: WARNING[Main]: Couldn't find a locale directory!
2017-02-16 21:08:37: WARNING[Main]: Your PostgreSQL server lacks UPSERT support. Use version 9.5 or better if possible.
Migrated 880260 blocks, 99.2227% completed.
2017-02-16 21:10:24: ACTION[Main]: Successfully migrated 887156 blocks
2017-02-16 21:10:24: ACTION[Main]: world.mt updated
Enjoy!
HOMETOWN -Our little server. Keep the HOMETOWN chatter @ http://hometownserver.com - Our server map: http://media.hometownserver.com
Re: minetestserver -migrate and postgresql?
Can rewrite your post to the wiki?
Interesting to redis too.
Interesting to redis too.
I am groot!
Re: minetestserver -migrate and postgresql?
Thanks for this post.
I'd migrate the player database as well:
I'd migrate the player database as well:
Code: Select all
pgsql_player_connection = host=<db_host> user=<db_user> password=<db_password> dbname=<db_name>
minetestserver --migrate-players <name of new backend> --world <path to your world directory>
- Edgy1
- Member
- Posts: 163
- Joined: Thu Nov 24, 2016 16:29
- GitHub: JamesTobin
- In-game: Trump
Re: minetestserver -migrate and postgresql?
Code: Select all
minetest@debian:~/minetest/bin$ ./minetestserver --migrate postgresql --world "/ home/minetest/minetest/worlds/Trumpland"
2018-07-22 17:29:32: WARNING[Main]: Couldn't find a locale directory!
2018-07-22 17:29:32: ERROR[Main]: An unhandled exception occurred: Database back end postgresql not supported.
2018-07-22 17:29:32: ERROR[Main]: In thread 7fab6f525b80:
2018-07-22 17:29:32: ERROR[Main]: /home/minetest/minetest/src/main.cpp:e2: int m ain(int, char**): A fatal error occured: Database backend postgresql not support ed.
2018-07-22 17:29:32: ERROR[Main]: Debug stacks:
2018-07-22 17:29:32: ERROR[Main]: DEBUG STACK FOR THREAD 7fab6f525b80:
2018-07-22 17:29:32: ERROR[Main]: #0 int main(int, char**)
Aborted
minetest@debian:~/minetest/bin$
visit https://edgy1.net
- ExeterDad
- Member
- Posts: 1717
- Joined: Sun Jun 01, 2014 20:00
- In-game: ExeterDad
- Location: New Hampshire U.S.A
Re: minetestserver -migrate and postgresql?
Your version of Minetest is not compiled with postgresql support. Either build yourself with postgres headers installed or find a package that is compiled with support.
HOMETOWN -Our little server. Keep the HOMETOWN chatter @ http://hometownserver.com - Our server map: http://media.hometownserver.com
- Edgy1
- Member
- Posts: 163
- Joined: Thu Nov 24, 2016 16:29
- GitHub: JamesTobin
- In-game: Trump
Re: minetestserver -migrate and postgresql?
How do you compile with postgress support?
visit https://edgy1.net
- ExeterDad
- Member
- Posts: 1717
- Joined: Sun Jun 01, 2014 20:00
- In-game: ExeterDad
- Location: New Hampshire U.S.A
Re: minetestserver -migrate and postgresql?
add:
- DENABLE_POSTGRESQL=1
to your cmake command :)
- DENABLE_POSTGRESQL=1
to your cmake command :)
HOMETOWN -Our little server. Keep the HOMETOWN chatter @ http://hometownserver.com - Our server map: http://media.hometownserver.com
- Edgy1
- Member
- Posts: 163
- Joined: Thu Nov 24, 2016 16:29
- GitHub: JamesTobin
- In-game: Trump
Re: minetestserver -migrate and postgresql?
thx i'll try soon. appreciate it.
by the way i live in the same state as you :)
by the way i live in the same state as you :)
visit https://edgy1.net
- ExeterDad
- Member
- Posts: 1717
- Joined: Sun Jun 01, 2014 20:00
- In-game: ExeterDad
- Location: New Hampshire U.S.A
Re: minetestserver -migrate and postgresql?
If I can find it... I have a handy script I made a couple years ago that still works. It will build you a .deb package, with or without the client or server. It will install all needed dev packages, download whatever git version your after, and build everything, make a legit .deb that you can install (or cleanly) remove in the system.Fedora24 wrote:thx i'll try soon. appreciate it.
by the way i live in the same state as you :)
If I find it, I'll send it your way.
HOMETOWN -Our little server. Keep the HOMETOWN chatter @ http://hometownserver.com - Our server map: http://media.hometownserver.com
- Edgy1
- Member
- Posts: 163
- Joined: Thu Nov 24, 2016 16:29
- GitHub: JamesTobin
- In-game: Trump
Re: minetestserver -migrate and postgresql?
Thanks exter :) appreciated
I got a lot of homework tonight so if I don't do it tonight I will probably tomorrow night.
I got a lot of homework tonight so if I don't do it tonight I will probably tomorrow night.
visit https://edgy1.net
- Edgy1
- Member
- Posts: 163
- Joined: Thu Nov 24, 2016 16:29
- GitHub: JamesTobin
- In-game: Trump
- bhree
- Member
- Posts: 169
- Joined: Tue Jun 19, 2018 07:45
- GitHub: bhree
- IRC: bhree
- In-game: bhree
- Location: Alone In The Wild Land
- Contact:
Re: minetestserver -migrate and postgresql?
I have succeeded migrating my map using above methods and it was a breeze. One question if I migrate players to postgresql also, will it create new table in the same database with map or should I create new database? Thanks.
[edit] my curiousity couldn't wait as I already have backup so I tried using map database. It seems players and map data can be in the same database.
[edit] my curiousity couldn't wait as I already have backup so I tried using map database. It seems players and map data can be in the same database.
-
- Member
- Posts: 47
- Joined: Sun Dec 12, 2021 01:55
- GitHub: lazerbeak12345
- In-game: lazerbeak12345
Re: minetestserver -migrate and postgresql?
Sorry to necropost... how do I specify a UNIX socket instead of the IP and port? looking to trim down a bit more latency.
Chocolate armor is tasty :)
Re: minetestserver -migrate and postgresql?
Like so:
pgsql_connection = postgresql:///DATABASENAME?host=/var/run/postgresql&user=USERNAME&password=PASSWORD&dbname=DATABASENAME
pgsql_connection = postgresql:///DATABASENAME?host=/var/run/postgresql&user=USERNAME&password=PASSWORD&dbname=DATABASENAME
Whatever I say is CC0
-
- Member
- Posts: 47
- Joined: Sun Dec 12, 2021 01:55
- GitHub: lazerbeak12345
- In-game: lazerbeak12345
Re: minetestserver -migrate and postgresql?
Thanks! I saw a massive performance improvement from this (as I expected). I'd recommend this for anyone. (of course, like in the other format, you can still wrap the values in quotes, like your password).
Chocolate armor is tasty :)
Who is online
Users browsing this forum: No registered users and 3 guests