Postgresql Backend

User avatar
Johnny Joy
Member
 
Posts: 43
Joined: Fri Sep 05, 2014 20:26
GitHub: johnnyjoy
In-game: jjb

Postgresql Backend

by Johnny Joy » Sun Jul 19, 2015 04:12

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

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
$ 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 Reading


How to compile from source

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.

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

Configuration

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/static/libpq-connect.html

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
backend            = postgresql
pg_connection_info = connection string, defaults to dbname=minetest


Database Authentication

How Minetest authenticates to the database is left up to the user.

See: http://www.postgresql.org/docs/9.4/static/auth-methods.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/static/libpq-pgpass.html

Schema

This 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 Creative

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.

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.
Last edited by Johnny Joy on Thu Jul 23, 2015 20:50, edited 14 times in total.
 

User avatar
ShadowNinja
Member
 
Posts: 194
Joined: Tue Jan 22, 2013 22:35
GitHub: ShadowNinja
IRC: ShadowNinja
In-game: ShadowNinja

Re: Postgresql Backend

by ShadowNinja » Sun Jul 19, 2015 20:24

I beat you to it. :-P
I wrote a PostgreSQL backend a few months ago, but didn't bother to put it in a PR.
It automatically sets up the tables and only requires one extra setting (postgresql_connection_info).
Here it is: https://github.com/ShadowNinja/minetest/tree/PostgreSQL
I play on my Minetest server and VanessaE's.
The best way to contact me is usually IRC (InchraNet, freenode).
 

User avatar
Johnny Joy
Member
 
Posts: 43
Joined: Fri Sep 05, 2014 20:26
GitHub: johnnyjoy
In-game: jjb

Re: Postgresql Backend

by Johnny Joy » Mon Jul 20, 2015 16:37

I actually wrote this last October, but waited to submit until now. I ran the idea past VanessaE when I did. However it's not a race. This is her reply from October 17th 2014. I waited for personal reasons, and to see when UPSERT would be included in distros. I hope clearly hoping for sooner rather than later. I intended to update with version detection so that UPSERT will be used in versions that support it.

If you would like, I can stop now, and move on to other things. I do not want to step on any toes. Especially if those toes have done so much work on something which makes so many people happy. Or, I if you like, I could present a reworked version that would incorporate elements from your implementation. It's entirely up to you.

Thanks, for everything you have done.

VanessaE wrote:You're quite welcome! I'm always happy to make stuff people can generally use, though my work with Minetest is limited mostly to mods, game content, and textures rather than the engine. As for PostgreSQL, actually I don't know the first thing about it. :-P

However, the need for backups is keenly felt pretty much everywhere, especially among Minetest server owners; I have a script that shuts my servers down nightly for backups (via rsync).

I'm sure others would find your idea useful. Be sure you discuss your idea with the folks in IRC, Freenode #minetest-dev just to make sure there aren't any naysayers that'll block your contributions from being merged. I am not among those - in my opinion, support for more backends is a good thing.

Also, if you get it working, be sure you make an appropriate backend driver for the overview mapper (https://github.com/minetest/minetestmapper) as well.

Good luck with it!


ShadowNinja wrote:I beat you to it. :-P
I wrote a PostgreSQL backend a few months ago, but didn't bother to put it in a PR.
It automatically sets up the tables and only requires one extra setting (postgresql_connection_info).
Here it is: https://github.com/ShadowNinja/minetest/tree/PostgreSQL
Last edited by Johnny Joy on Mon Jul 20, 2015 17:33, edited 1 time in total.
 

User avatar
Johnny Joy
Member
 
Posts: 43
Joined: Fri Sep 05, 2014 20:26
GitHub: johnnyjoy
In-game: jjb

Re: Postgresql Backend

by Johnny Joy » Mon Jul 20, 2015 16:45

I'm going to rework my code, if only for the practise.
 

User avatar
Minetestforfun
Member
 
Posts: 936
Joined: Tue Aug 05, 2014 14:09
GitHub: Darcidride
IRC: Darcidride + MinetestForFun
In-game: Darcidride + MinetestForFun

Re: Postgresql Backend

by Minetestforfun » Tue Jul 21, 2015 14:39

Wow great work /docs/tutorials :)
but since mtmapper doesn't use the psotgresql backend, i will not use your database backend

Any ports like that for MariaDB(or MySQL :S) was made in the past ?
 

User avatar
Johnny Joy
Member
 
Posts: 43
Joined: Fri Sep 05, 2014 20:26
GitHub: johnnyjoy
In-game: jjb

Re: Postgresql Backend

by Johnny Joy » Tue Jul 21, 2015 21:40

If nobody else is working on a MariaDB/MySQL backend, I would love to make one.

You are right about minetestmapper. That, and rollback, would be the next targets. In that order. I hope that by using last modified column that map updates should be faster, since only the blocks that have changed would need to be updated in the map.

The think the ideal setup would be a database class that could be used by the minetest server, rollback, and minetest mapper, so that all of the code for a given backend would be conveniently located in the same place.

Minetestforfun wrote:Wow great work /docs/tutorials :)
but since mtmapper doesn't use the psotgresql backend, i will not use your database backend

Any ports like that for MariaDB(or MySQL :S) was made in the past ?
 

User avatar
Johnny Joy
Member
 
Posts: 43
Joined: Fri Sep 05, 2014 20:26
GitHub: johnnyjoy
In-game: jjb

Re: Postgresql Backend

by Johnny Joy » Thu Jul 23, 2015 00:50

ShadowNinja wrote:I beat you to it. :-P
I wrote a PostgreSQL backend a few months ago, but didn't bother to put it in a PR.
It automatically sets up the tables and only requires one extra setting (postgresql_connection_info).
Here it is: https://github.com/ShadowNinja/minetest/tree/PostgreSQL


I have reworked my version of the backend, and I was hoping to get your honest opinion. I tried your upsert SQL, however it was painfully slow and caused issues with data quality. The update, followed by the insert worked best for saveBlocks. I also added support for PG 9.5 so that these can be combined into a single transaction. The feature is automatic, when 9.5 is detected.

https://github.com/minetest/minetest/pull/2912/files
 

User avatar
Johnny Joy
Member
 
Posts: 43
Joined: Fri Sep 05, 2014 20:26
GitHub: johnnyjoy
In-game: jjb

Re: Postgresql Backend

by Johnny Joy » Thu Jul 23, 2015 00:57

Is anyone willing to test the code? Looking for Windows & Linux testers.
 

User avatar
Johnny Joy
Member
 
Posts: 43
Joined: Fri Sep 05, 2014 20:26
GitHub: johnnyjoy
In-game: jjb

Re: Postgresql Backend

by Johnny Joy » Thu Jul 23, 2015 03:58

ShadowNinja wrote:I beat you to it. :-P
I wrote a PostgreSQL backend a few months ago, but didn't bother to put it in a PR.
It automatically sets up the tables and only requires one extra setting (postgresql_connection_info).
Here it is: https://github.com/ShadowNinja/minetest/tree/PostgreSQL


I didn't really think that making the end user create the connection string was an advantage, since in many cases, such as a private server, it's not something that end user should know how to do. Honestly, I'm not sure that many people would want to do that. However, if it's a deal breaker I could change that.
 

User avatar
ShadowNinja
Member
 
Posts: 194
Joined: Tue Jan 22, 2013 22:35
GitHub: ShadowNinja
IRC: ShadowNinja
In-game: ShadowNinja

Re: Postgresql Backend

by ShadowNinja » Sat Jul 25, 2015 17:02

Minetestforfun: A MySQL backend was made when database backends were first made plugable, but it wasn't included in the final code, possibly because MySQL is bad.
I play on my Minetest server and VanessaE's.
The best way to contact me is usually IRC (InchraNet, freenode).
 

User avatar
Johnny Joy
Member
 
Posts: 43
Joined: Fri Sep 05, 2014 20:26
GitHub: johnnyjoy
In-game: jjb

Re: Postgresql Backend

by Johnny Joy » Tue Jul 28, 2015 16:18

I agree, however now that Oracle has chased off the MySQL team many people have been hoping their fork will be far better than MySQL. This makes MySQL compatibility more attractive. Oracle currently shows zero interest in fixing the many issues with MySQL that prevent it from being a production ready product.

ShadowNinja wrote:Minetestforfun: A MySQL backend was made when database backends were first made plugable, but it wasn't included in the final code, possibly because MySQL is bad.
 

User avatar
Minetestforfun
Member
 
Posts: 936
Joined: Tue Aug 05, 2014 14:09
GitHub: Darcidride
IRC: Darcidride + MinetestForFun
In-game: Darcidride + MinetestForFun

Re: Postgresql Backend

by Minetestforfun » Sat Aug 01, 2015 19:24

I don't need/want a MySQL backend, i don't like it because of Oracle... (i don't click your link, don't worry i'm informed :p)

I need a MariaDB backend, but for now MySQL and MariaDB are fully compatible (i've migrate many database from MySQL to MariaDB in the last year, i've never hencounter problems with it, the steps are easy and fast, thanks to MariaDB)

Hope you will try to do this someday Johnny Joy :)
Last edited by Minetestforfun on Fri Aug 21, 2015 09:20, edited 3 times in total.
 

User avatar
Prot
Member
 
Posts: 36
Joined: Thu Apr 02, 2015 13:20
GitHub: EuPhobos
In-game: EuPhobos

Re: Postgresql Backend

by Prot » Thu Aug 20, 2015 12:45

wow.. i wan't to test it!
But i need some help, i cannot build minetest with psql.
First, i got this error:
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
/mnt/uncrypt/Games/minetest_psql/src/database-postgresql.cpp:114:21: warning: character constant too long for its type
  if (conn_info.find('application_name') == std::string::npos)
                     ^
/mnt/uncrypt/Games/minetest_psql/src/database-postgresql.cpp: In member function ‘void Database_PostgreSQL::connectString(Settings&)’:
/mnt/uncrypt/Games/minetest_psql/src/database-postgresql.cpp:114:39: warning: overflow in implicit constant conversion [-Woverflow]
  if (conn_info.find('application_name') == std::string::npos)

Then i change quotes to doublequotes in 114 line of database-postgresql.cpp file and no more this error, but i got next message from linker at the end:
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
[100%] Building CXX object src/CMakeFiles/minetest.dir/wieldmesh.cpp.o
Linking CXX executable ../bin/minetest
CMakeFiles/minetest.dir/database-postgresql.cpp.o: In function `Database_PostgreSQL::initialized()':
database-postgresql.cpp:(.text+0x9): undefined reference to `PQstatus'
CMakeFiles/minetest.dir/database-postgresql.cpp.o: In function `Database_PostgreSQL::~Database_PostgreSQL()':
database-postgresql.cpp:(.text+0x35): undefined reference to `PQfinish'
CMakeFiles/minetest.dir/database-postgresql.cpp.o: In function `Database_PostgreSQL::~Database_PostgreSQL()':
database-postgresql.cpp:(.text+0xd5): undefined reference to `PQfinish'
CMakeFiles/minetest.dir/database-postgresql.cpp.o: In function `Database_PostgreSQL::dbconnect()':
database-postgresql.cpp:(.text+0x58e): undefined reference to `PQconnectdb'
database-postgresql.cpp:(.text+0x59a): undefined reference to `PQstatus'
database-postgresql.cpp:(.text+0x5ab): undefined reference to `PQserverVersion'
database-postgresql.cpp:(.text+0x669): undefined reference to `PQerrorMessage'
CMakeFiles/minetest.dir/database-postgresql.cpp.o: In function `Database_PostgreSQL::ping()':
database-postgresql.cpp:(.text+0x7ce): undefined reference to `PQping'
database-postgresql.cpp:(.text+0x7f4): undefined reference to `PQerrorMessage'
CMakeFiles/minetest.dir/database-postgresql.cpp.o: In function `Database_PostgreSQL::resultsCheck(pg_result*, bool)':
database-postgresql.cpp:(.text+0x8cf): undefined reference to `PQresultStatus'
database-postgresql.cpp:(.text+0x8e4): undefined reference to `PQclear'
database-postgresql.cpp:(.text+0x903): undefined reference to `PQresultErrorMessage'
CMakeFiles/minetest.dir/database-postgresql.cpp.o: In function `Database_PostgreSQL::execPrepared(std::string, int, irr::core::vector3d<short> const&, bool, std::string const&)':
database-postgresql.cpp:(.text+0x1351): undefined reference to `PQexecPrepared'
CMakeFiles/minetest.dir/database-postgresql.cpp.o: In function `Database_PostgreSQL::loadBlock(irr::core::vector3d<short> const&)':
database-postgresql.cpp:(.text+0x1f84): undefined reference to `PQntuples'
database-postgresql.cpp:(.text+0x1f94): undefined reference to `PQgetlength'
database-postgresql.cpp:(.text+0x1fa3): undefined reference to `PQgetvalue'
database-postgresql.cpp:(.text+0x1fdf): undefined reference to `PQclear'
CMakeFiles/minetest.dir/database-postgresql.cpp.o: In function `Database_PostgreSQL::prepare()':
database-postgresql.cpp:(.text+0x2154): undefined reference to `PQprepare'
database-postgresql.cpp:(.text+0x21c8): undefined reference to `PQprepare'
database-postgresql.cpp:(.text+0x223c): undefined reference to `PQprepare'
database-postgresql.cpp:(.text+0x2313): undefined reference to `PQprepare'
CMakeFiles/minetest.dir/database-postgresql.cpp.o: In function `Database_PostgreSQL::verifyDatabase()':
database-postgresql.cpp:(.text+0x259e): undefined reference to `PQstatus'
database-postgresql.cpp:(.text+0x25b5): undefined reference to `PQreset'
database-postgresql.cpp:(.text+0x25be): undefined reference to `PQping'
database-postgresql.cpp:(.text+0x25d8): undefined reference to `PQerrorMessage'
CMakeFiles/minetest.dir/database-postgresql.cpp.o: In function `Database_PostgreSQL::endSave()':
database-postgresql.cpp:(.text+0x26b0): undefined reference to `PQexec'
database-postgresql.cpp:(.text+0x26bb): undefined reference to `PQresultStatus'
database-postgresql.cpp:(.text+0x26cb): undefined reference to `PQclear'
database-postgresql.cpp:(.text+0x26e7): undefined reference to `PQresultErrorMessage'
CMakeFiles/minetest.dir/database-postgresql.cpp.o: In function `Database_PostgreSQL::prepareStatement(std::string, std::string)':
database-postgresql.cpp:(.text+0x27c6): undefined reference to `PQprepare'
database-postgresql.cpp:(.text+0x27d1): undefined reference to `PQresultStatus'
database-postgresql.cpp:(.text+0x27e1): undefined reference to `PQclear'
database-postgresql.cpp:(.text+0x27fd): undefined reference to `PQresultErrorMessage'
CMakeFiles/minetest.dir/database-postgresql.cpp.o: In function `Database_PostgreSQL::beginSave()':
database-postgresql.cpp:(.text+0x28de): undefined reference to `PQstatus'
database-postgresql.cpp:(.text+0x28eb): undefined reference to `PQreset'
database-postgresql.cpp:(.text+0x2901): undefined reference to `PQexec'
database-postgresql.cpp:(.text+0x290c): undefined reference to `PQresultStatus'
database-postgresql.cpp:(.text+0x291c): undefined reference to `PQclear'
database-postgresql.cpp:(.text+0x2938): undefined reference to `PQresultErrorMessage'
CMakeFiles/minetest.dir/database-postgresql.cpp.o: In function `Database_PostgreSQL::createDatabase()':
database-postgresql.cpp:(.text+0x2a15): undefined reference to `PQexec'
database-postgresql.cpp:(.text+0x2a20): undefined reference to `PQresultStatus'
database-postgresql.cpp:(.text+0x2a34): undefined reference to `PQntuples'
database-postgresql.cpp:(.text+0x2a4a): undefined reference to `PQexec'
database-postgresql.cpp:(.text+0x2a68): undefined reference to `PQexec'
database-postgresql.cpp:(.text+0x2a86): undefined reference to `PQexec'
database-postgresql.cpp:(.text+0x2b24): undefined reference to `PQclear'
database-postgresql.cpp:(.text+0x2b42): undefined reference to `PQresultErrorMessage'
CMakeFiles/minetest.dir/database-postgresql.cpp.o: In function `Database_PostgreSQL::listAllLoadableBlocks(std::vector<irr::core::vector3d<short>, std::allocator<irr::core::vector3d<short> > >&)':
database-postgresql.cpp:(.text+0x2c9e): undefined reference to `PQexec'
database-postgresql.cpp:(.text+0x2cb6): undefined reference to `PQntuples'
database-postgresql.cpp:(.text+0x2ceb): undefined reference to `PQgetvalue'
database-postgresql.cpp:(.text+0x2d0c): undefined reference to `PQgetvalue'
database-postgresql.cpp:(.text+0x2d2a): undefined reference to `PQgetvalue'
database-postgresql.cpp:(.text+0x2d84): undefined reference to `PQclear'
collect2: error: ld returned 1 exit status
src/CMakeFiles/minetest.dir/build.make:4778: ошибка выполнения рецепта для цели «bin/minetest»
make[2]: *** [bin/minetest] Ошибка 1
CMakeFiles/Makefile2:132: ошибка выполнения рецепта для цели «src/CMakeFiles/minetest.dir/all»
make[1]: *** [src/CMakeFiles/minetest.dir/all] Ошибка 2
Makefile:137: ошибка выполнения рецепта для цели «all»
make: *** [all] Ошибка 2

I have 9.4+165 version of psql, and installed packets:
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
$ aptitude search postgres | grep ^i
i   postgresql                      - object-relational SQL database (supported
i A postgresql-9.4                  - object-relational SQL database, version 9.
i   postgresql-client               - front-end programs for PostgreSQL (support
i A postgresql-client-9.4           - front-end programs for PostgreSQL 9.4     
i A postgresql-client-common        - управление несколькими клиентскими версиям
i A postgresql-common               - управление кластером баз данных PostgreSQL
i   postgresql-server-dev-9.4       - development files for PostgreSQL 9.4 serve
i   postgresql-server-dev-all       - extension build tool for multiple PostgreS
 

User avatar
Prot
Member
 
Posts: 36
Joined: Thu Apr 02, 2015 13:20
GitHub: EuPhobos
In-game: EuPhobos

Re: Postgresql Backend

by Prot » Thu Aug 20, 2015 13:55

Pardon me, fixed allready by adding -lpq for linker flags in cmake-qt-gui
Worked flawless! I allready love this backend =)
ctime and mtime that's all what i need for online mapper.
And, as the database has x,y,z separatly, can we add more room for our 64km long world? ;)
 

User avatar
Johnny Joy
Member
 
Posts: 43
Joined: Fri Sep 05, 2014 20:26
GitHub: johnnyjoy
In-game: jjb

Re: Postgresql Backend

by Johnny Joy » Sat Aug 22, 2015 03:24

Sorry. I had been away on a business related trip.

Please describe the build environment, so I can address the issue.

Thanks, and I'm glad you like it.

Prot wrote:Pardon me, fixed allready by adding -lpq for linker flags in cmake-qt-gui
Worked flawless! I allready love this backend =)
ctime and mtime that's all what i need for online mapper.
And, as the database has x,y,z separatly, can we add more room for our 64km long world? ;)
 

User avatar
Johnny Joy
Member
 
Posts: 43
Joined: Fri Sep 05, 2014 20:26
GitHub: johnnyjoy
In-game: jjb

Re: Postgresql Backend

by Johnny Joy » Sat Aug 22, 2015 03:28

About mapping, I'm working on minetestmapper at the moment and hope to have something to submit by next weekend. Next, I would like to do incremental map updates that would use the mtime to update only the parts of the map that have changed since the last mapping. If the load is as little as I expect, this would mean that the map could be kept up to date every 60 seconds or so.


Prot wrote:Pardon me, fixed allready by adding -lpq for linker flags in cmake-qt-gui
Worked flawless! I allready love this backend =)
ctime and mtime that's all what i need for online mapper.
And, as the database has x,y,z separatly, can we add more room for our 64km long world? ;)
 

User avatar
Prot
Member
 
Posts: 36
Joined: Thu Apr 02, 2015 13:20
GitHub: EuPhobos
In-game: EuPhobos

Re: Postgresql Backend

by Prot » Thu Aug 27, 2015 11:41

My build environment:
debian 8.1 jessie
cmake 3.0.2-1
make 4.0-8.1
gcc version 4.9.2
I don't know what else to describe, but the problem is solved by adding the flag in cmake-gui on CMAKE_EXE_LINKER_FLAGS -lpq
---
By the way I noticed another small bug, when I tried to connect a second world in a separate database.
After i create database "test_psql" and world with same name, i wrote line in world.mt:
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
gameid = minetest
creative_mode = true
enable_damage = true
backend=postgresql
pg_connection_info = postgresql:///test_psql

Then i have the error:
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
-------------
  Separator 
-------------

2015-08-26 22:38:14: ERROR[main]: ERROR: An unhandled exception occurred: PostgreSQL database error: ВАЖНО:  база данных "test_psql application_name=minetest" не существует

psql say's, that database with name "test_psql application_name=minetest" not exists..
Then i try change connect string on "pg_connection_info = postgresql:///test_psql?" and get error that databese with name " application_name" not exists.
I fix this problem by writing application_name in connection string:
"pg_connection_info = postgresql:///test_psql?application_name=minetest" - this is solve my problem and workaround this bug.

Now i continue tests the psql backend, start server and allready fly around from the one corner-edge of the world to another, with generate tons of blocks and nodes, it's work nice =)
Ahh.. The "infinite" 64km³ world is pretty small.. =))
How about mapper? ;)
 

User avatar
Johnny Joy
Member
 
Posts: 43
Joined: Fri Sep 05, 2014 20:26
GitHub: johnnyjoy
In-game: jjb

Re: Postgresql Backend

by Johnny Joy » Thu Aug 27, 2015 19:51

Thanks. This is a huge help.

I will load up a machine to match your environment, and correct the flaw.

Prot wrote:My build environment:
debian 8.1 jessie
cmake 3.0.2-1
make 4.0-8.1
gcc version 4.9.2
I don't know what else to describe, but the problem is solved by adding the flag in cmake-gui on CMAKE_EXE_LINKER_FLAGS -lpq
---
By the way I noticed another small bug, when I tried to connect a second world in a separate database.
After i create database "test_psql" and world with same name, i wrote line in world.mt:
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
gameid = minetest
creative_mode = true
enable_damage = true
backend=postgresql
pg_connection_info = postgresql:///test_psql

Then i have the error:
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
-------------
  Separator 
-------------

2015-08-26 22:38:14: ERROR[main]: ERROR: An unhandled exception occurred: PostgreSQL database error: ВАЖНО:  база данных "test_psql application_name=minetest" не существует

psql say's, that database with name "test_psql application_name=minetest" not exists..
Then i try change connect string on "pg_connection_info = postgresql:///test_psql?" and get error that databese with name " application_name" not exists.
I fix this problem by writing application_name in connection string:
"pg_connection_info = postgresql:///test_psql?application_name=minetest" - this is solve my problem and workaround this bug.

Now i continue tests the psql backend, start server and allready fly around from the one corner-edge of the world to another, with generate tons of blocks and nodes, it's work nice =)
Ahh.. The "infinite" 64km³ world is pretty small.. =))
How about mapper? ;)
 

User avatar
Prot
Member
 
Posts: 36
Joined: Thu Apr 02, 2015 13:20
GitHub: EuPhobos
In-game: EuPhobos

Re: Postgresql Backend

by Prot » Sun Aug 30, 2015 18:25

Unfortunately I have some bad news.. I tested the server for almost 2 weeks, and came across 2 strange glitch.. the first time I used a map generator "valley" and got twice the error in not enough memory. Then I launched the "pure" server without mods, after 3 days I had a very hard lag in the download chunk/blocks of the map by client, after another day, the download just stopped, there was one big lag. Moreover, the load on the server was not Postgress rested, and as the disk system and the processor, LA of the server the ~0.10. I checked the secondary server which works with sqlite backend on the same server, it worked as before normally. As soon as I restart again the server with postgres as he again began to work normally.
I record video of my action about server..
https://youtu.be/NH9zZN3iY-k
This is not fully shown the work of server, but i test psql separetly. The "select * from blocks" tooks about 0.300ms to select all ~1,200,000 blocks(or ~5,000,000,000 nodes) of server map..
I not see any of source of problem the lag, but the lag is present..
At this moment, I don't known where is the problem hide..
Tested on machines, client:
AMD FX(tm)-8150 Eight-Core Processor
16 Gb RAM
on server side:
AMD FX(tm)-6350 Six-Core Processor
24 Gb RAM
 

User avatar
Prot
Member
 
Posts: 36
Joined: Thu Apr 02, 2015 13:20
GitHub: EuPhobos
In-game: EuPhobos

Re: Postgresql Backend

by Prot » Fri Sep 11, 2015 14:41

This was strange bug, it's dissapear. I have 8 days of the server uptime without problems
 

User avatar
Kociak
Member
 
Posts: 17
Joined: Fri Apr 03, 2015 20:29
In-game: kociak

Re: Postgresql Backend

by Kociak » Fri Sep 11, 2015 21:18

Actually I had very strange dumps. Backend runs fluently... but...
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
 <<snip-snip>>

2015-09-11 23:10:06: ERROR[main]: UNRECOVERABLE error occurred. Stopping server. Please fix the following error:
2015-09-11 23:10:06: ERROR[main]: Lua: OOM error from mod 'mg' in callback environment_OnGenerated(): not enough memory
2015-09-11 23:10:06: ERROR[main]: Current Lua memory usage: 1133 MB

In thread 7f2856287760:
/home/trishina/minetest/src/server.cpp:511: void Server::step(float): A fatal error occurred: Lua: OOM error from mod 'mg' in callback environment_OnGenerated(): not enough memory
Current Lua memory usage: 1133 MB
Debug stacks:
DEBUG STACK FOR THREAD 7f284d0eb700:
#0  virtual void* EmergeThread::Thread()
(Leftover data: #1  virtual MapBlock* ServerMap::emergeBlock(v3s16, bool): p=(-8,8,-8), create_blank=0)
DEBUG STACK FOR THREAD 7f284d8ec700:
#0  virtual void* EmergeThread::Thread()
(Leftover data: #1  virtual MapBlock* ServerMap::emergeBlock(v3s16, bool): p=(-8,8,-8), create_blank=0)
(Leftover data: #2  MapBlock* ServerMap::loadBlock(v3s16))
(Leftover data: #3  void ServerMap::loadBlock(std::string*, v3s16, MapSector*, bool))
DEBUG STACK FOR THREAD 7f284f2bd700:
#0  virtual void* ServerThread::Thread()
#1  void Server::AsyncRunStep(bool)
#2  virtual void ServerEnvironment::step(float)
(Leftover data: #3  void RemoteClient::GetNextBlocks(ServerEnvironment*, EmergeManager*, float, std::vector<PrioritySortedBlockTransfer>&))
(Leftover data: #4  void ItemStack::serialize(std::ostream&) const)
(Leftover data: #5  bool getCraftingResult(Inventory*, ItemStack&, std::vector<ItemStack>&, bool, IGameDef*))
DEBUG STACK FOR THREAD 7f2856287760:
#0  int main(int, char**)
#1  Dedicated server branch
#2  void dedicated_server_loop(Server&, bool&)
#3  void Server::step(float)

Actually it doesn't matter if MG is present or not... Always a crash...
Strange enough - it happens when server generates new chunks...

List of mods here:
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
3d_armor        bucket       cottages       extranodes          funnysigns          hydro           mesecons     mp_streets         scaffolding     stairs       titanium           various    xdoors
3dforniture     buildtest    craft_guide    fallout_billboards  gauges              industrial      metals       nature             screwdriver     stargate     tnt                vehicles   xfences
animatedfluids  car_126r     creative       fancy_elevator      give_initial_stuff  infrastructure  mg           oil                secret_passage  str_helpers  trash_can          vessels    xladder
awards          car_f1       default        farming             glowblocks          item_drop       mobs         paintings          sethome         tablemod     travelnet          weather    xpanes
beds            carts        digilines      fire                growing_trees       jumping         monorail     pipeworks          shields         technic      tronblocks         wieldview
boats           castle_wall  doors          flowers             gt1111              lift            moreblocks   plantlife_modpack  sign            telegate     unifiedbricks      windmill
bones           caverealms   dye            forceload           highlandpools       madblocks       more_fences  player_textures    sliding_door    teleporter   unifieddyes        wireless
bottle          cg1111       explore_map    forth_computer      homedecor           mapit           moreores     protector          snow            teleporters  unified_inventory  wool
bridges         concrete     explorertools  framedglass         hovers              maptools        moretrees    pushable_block     snowdrift       throwing     urban              worldedit
 

User avatar
Prot
Member
 
Posts: 36
Joined: Thu Apr 02, 2015 13:20
GitHub: EuPhobos
In-game: EuPhobos

Re: Postgresql Backend

by Prot » Fri Sep 25, 2015 08:21

Any news?
 

User avatar
Kociak
Member
 
Posts: 17
Joined: Fri Apr 03, 2015 20:29
In-game: kociak

Re: Postgresql Backend

by Kociak » Thu Oct 29, 2015 19:21

OK, I've got one running. It runs _very_ smoothly. Better than sqlite. I really don't know what happened, maybe "mg" was the guilt one...
We've got weeks of uptime (well, unless there is a faulty mod...)
Keep up the good work!
 

User avatar
Prot
Member
 
Posts: 36
Joined: Thu Apr 02, 2015 13:20
GitHub: EuPhobos
In-game: EuPhobos

Re: Postgresql Backend

by Prot » Tue Nov 17, 2015 11:13

I had a similar problem at the beginning. When I used TCP/IP to access psql and valley mapgen. Now I switched to UNIX socket and ran 2 servers, the standard minetest_game, and a server with a bunch of mods, including the ethereal mapgen. For more than a month without falling. You can try it at the public server lists "EuPhobos PostgreSQL tests" and "EuPhobos creative psql"
I would like to convert my old server from sqlite to psql, but something the author of this project does not respond, I'm afraid the project is dead. =(
 

Argos
Member
 
Posts: 40
Joined: Tue Mar 04, 2014 21:47

Re: Postgresql Backend

by Argos » Wed Dec 16, 2015 16:05

FYI: I created a postgresql branch on my version of minetestmapper. It supports both johnnyjoy's implementation and ShadowNinja's implementation.

The following patch was required to compile minetest + postgresql (johnnyjoy's version):
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
diff --git a/src/CMakeLists.txt b/src/CMakeLists.txt
index 82a755c..07c0b51 100644
--- a/src/CMakeLists.txt
+++ b/src/CMakeLists.txt
@@ -570,6 +570,9 @@ if(BUILD_CLIENT)
        if (USE_LEVELDB)
                target_link_libraries(${PROJECT_NAME} ${LEVELDB_LIBRARY})
        endif()
+       if (USE_POSTGRESQL)
+               target_link_libraries(${PROJECT_NAME} ${POSTGRESQL_LIBRARY})
+       endif()
        if (USE_REDIS)
                target_link_libraries(${PROJECT_NAME} ${REDIS_LIBRARY})
        endif()


WRT the code, I think using a default database name is not a good idea. It seems to me that this could (i.e. would, at some point) cause different worlds to automatically use the same database. If two of those run simultaneously, they would probably misbehave or even crash. If such a thing happens, it should be caused by an explicit user mistake, not as a result of forgetting to set a configuration option, or as a result of not even knowing is should be set (who reads the manual, after all...)

Also consider: the user might already have a database named 'minetest', with a different purpose.
My mods & tools:
My fork of Minetestmapper - much improved - features, manual, Windows downloads
[MOD] Wrench (rotate:wrench) - improved screwdriver alternative - manual, download
 

User avatar
Prot
Member
 
Posts: 36
Joined: Thu Apr 02, 2015 13:20
GitHub: EuPhobos
In-game: EuPhobos

Re: Postgresql Backend

by Prot » Wed Dec 16, 2015 17:29

Argos wrote:FYI: I created a postgresql branch on my version of minetestmapper. It supports both johnnyjoy's implementation and ShadowNinja's implementation.


I don't understand how to use it..

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
$ git clone https://github.com/Rogier-5/minetest-mapper-cpp.git
$ cd minetest-mapper-cpp
$ git checkout postgresql
$ mkdir build
$ cd build
$ cmake ..
$ make -j 8
$ ./minetestmapper -i /mnt/extrabig/minetest/test_psql/worlds/test_psql -o .
Exception: Failed to find or failed to open a colors.txt file.
$ find /mnt/extrabig/minetest/test_psql -name colors.txt
/mnt/extrabig/minetest/test_psql/util/colors.txt
$ ./minetestmapper --color /mnt/extrabig/minetest/test_psql/util/colors.txt -i /mnt/extrabig/minetest/test_psql/worlds/test_psql -o .
Exception: unable to open database file, Database file: /mnt/extrabig/minetest/test_psql/worlds/test_psql/map.sqlite
$ ./minetestmapper --postgress --color /mnt/extrabig/minetest/test_psql/util/colors.txt -i /mnt/extrabig/minetest/test_psql/worlds/test_psql -o .
./minetestmapper: unrecognized option '--postgress'


By the way, i have 2 servers with this backend, and of course each world use diferrent database name, not default 'minetest', this settings i wrate in world.mt
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
$ cat test_psql/worlds/test_psql/world.mt
gameid = minetest
creative_mode = true
enable_damage = true
backend=postgresql
pg_connection_info = postgresql:///test_psql?application_name=minetest

$ cat creative_psql/worlds/creative_psql/world.mt
gameid = minetest
backend = postgresql
pg_connection_info = postgresql:///creative_psql?application_name=minetest
 

Argos
Member
 
Posts: 40
Joined: Tue Mar 04, 2014 21:47

Re: Postgresql Backend

by Argos » Wed Dec 16, 2015 17:56

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
$ ./minetestmapper --color /mnt/extrabig/minetest/test_psql/util/colors.txt -i /mnt/extrabig/minetest/test_psql/worlds/test_psql -o .
Exception: unable to open database file, Database file: /mnt/extrabig/minetest/test_psql/worlds/test_psql/map.sqlite


Thanks for the bugreport :-) I'll fix it.

As a workaround, make sure there are spaces around the '=' in world.mt. I.e.:
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
backend = postgresql


Edit: I fixed the problem & pushed a new version.

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
$ ./minetestmapper --postgress --color /mnt/extrabig/minetest/test_psql/util/colors.txt -i /mnt/extrabig/minetest/test_psql/worlds/test_psql -o .
./minetestmapper: unrecognized option '--postgress'


Just like for the regular version of minetestmapper, the option to select a backend is '--backend <backend-type>'. Type 'minestmapper --help' for a list of options, or see the manual (in doc/) for a full description.

Note that the argument to -o should be a filename for the map. E.g.: '-o map.png'.
My mods & tools:
My fork of Minetestmapper - much improved - features, manual, Windows downloads
[MOD] Wrench (rotate:wrench) - improved screwdriver alternative - manual, download
 

User avatar
Johnny Joy
Member
 
Posts: 43
Joined: Fri Sep 05, 2014 20:26
GitHub: johnnyjoy
In-game: jjb

Re: Postgresql Backend

by Johnny Joy » Mon May 02, 2016 23:55

I've logged in and it looks great.

Prot wrote:I had a similar problem at the beginning. When I used TCP/IP to access psql and valley mapgen. Now I switched to UNIX socket and ran 2 servers, the standard minetest_game, and a server with a bunch of mods, including the ethereal mapgen. For more than a month without falling. You can try it at the public server lists "EuPhobos PostgreSQL tests" and "EuPhobos creative psql"
I would like to convert my old server from sqlite to psql, but something the author of this project does not respond, I'm afraid the project is dead. =(
 

User avatar
Johnny Joy
Member
 
Posts: 43
Joined: Fri Sep 05, 2014 20:26
GitHub: johnnyjoy
In-game: jjb

Re: Postgresql Backend

by Johnny Joy » Mon May 02, 2016 23:58

This is great information. I would have been further along, but working too much cause a repetitive stress injury to my right arm and I had to go cold turkey on computes for a while, and then only with my left hand. I'm back now and updating my pull. This is great Argos!

Argos wrote:
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
$ ./minetestmapper --color /mnt/extrabig/minetest/test_psql/util/colors.txt -i /mnt/extrabig/minetest/test_psql/worlds/test_psql -o .
Exception: unable to open database file, Database file: /mnt/extrabig/minetest/test_psql/worlds/test_psql/map.sqlite


Thanks for the bugreport :-) I'll fix it.

As a workaround, make sure there are spaces around the '=' in world.mt. I.e.:
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
backend = postgresql


Edit: I fixed the problem & pushed a new version.

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
$ ./minetestmapper --postgress --color /mnt/extrabig/minetest/test_psql/util/colors.txt -i /mnt/extrabig/minetest/test_psql/worlds/test_psql -o .
./minetestmapper: unrecognized option '--postgress'


Just like for the regular version of minetestmapper, the option to select a backend is '--backend <backend-type>'. Type 'minestmapper --help' for a list of options, or see the manual (in doc/) for a full description.

Note that the argument to -o should be a filename for the map. E.g.: '-o map.png'.
 

nrz
Member
 
Posts: 51
Joined: Sat Feb 07, 2015 17:16
GitHub: nerzhul
IRC: nrzkt
In-game: nrz

Re: Postgresql Backend

by nrz » Sun May 22, 2016 12:45

We now have a postgresql backend in core:

https://github.com/minetest/minetest/co ... ea3db02b78

This backend uses postgresql 9.5 upserts to have the maximum map updating performance.
 

Next

Return to Minetest Engine

Who is online

Users browsing this forum: No registered users and 3 guests

cron