How to properly import a database

[herrdeh (https://tryton-dach.org)]

When importing a database dump by something like

psql -d database_name < DB_dump_name.sql

in ubuntu and debian I get the error message:

$ psql -U DB_user -d database_name < DB_dump_name.sql psql: error: connection server to Socket »/var/run/postgresql/.s.PGSQL.5432« failed: FATAL: Peer authentication failed for user "DB_user"

This can be fixed by changing in /etc/postgresql/<your-version>/main/pg_hba.conf

local all all peer

to

local all all md5

But this solution may have security concerns, is difficult to handle within an install script and may be confusing when (as in my system) there are various postgres versions.

In priciple, this can be solved by doing the dump import by the ‘postgres’ user:

sudo -u postgres -- psql -d database_name < DB_dump_name.sql

Problem is that the the trytond-admin [...] --all command then fails with

psycopg2.errors.DuplicateTable: relation "ir_configuration_id_seq" already exists.

We discussed that problem broadly here, but I now do notice that those days I avoided the problem by changing pg_hba.conf.

Do we have a proper solution with a better pqsl import command? @edbo, guess this is your playground… (;

Cheers,
Wolf

[edbo]

@herrdeh

Do we have a proper solution with a better pqsl import command? @edbo, guess this is your playground… (;

Yeay!! I love it playing around with that stuff and help people.

@herrdeh

psql -U DB_user -d database_name < DB_dump_name.sql

Add -h localhost to the command so no changes are needed in the pg_hba.conf.

psql -U DB_user -d database_name -h localhost < DB_dump_name.sql

[herrdeh (https://tryton-dach.org)]

This seems to work very well - and as so often, Mr. Eddy is my personal hero of the day (;
Thank you, my friend!