How to properly import a database

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


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… (;


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

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

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

This topic was automatically closed 12 hours after the last reply. New replies are no longer allowed.