Errors when moving PostgreSQL databases
I am trying to dump and import a database to another user on different hosts using PostgreSQL 9.4.
I followed what was said in multiple answers here, however I am still puzzled by one part.
I dump the original database with the following command:
pg_dump -d test --clean --no-owner -f test.sql
On the target machine, after clear startup (initdb), I do the following:
1. createuser test
2. createdb -O test test
3.Now psql -l
displays test case as name and owner
4. Now if I try to import using:, psql -U test -d test -f test.sql
I get a huge amount of errors like:
ERROR: relation "public.x" does not exist
ERROR: index "y" does not exist
ERROR: sequence "z" does not exist
ERROR: table "u" does not exist
ERROR: must be owner of extension plpgsql
ERROR: must be owner of schema public
ERROR: schema "public" already exists
However, even with all these errors, the import seems to complete and the dump execution on the target computer ends up with an identical sql file for the source (except for the two superuser lines at the very end).
Should I be worried about these errors? Why is this happening and what should I do differently to avoid these errors? Is this the recommended way to move databases between servers / users?
source to share
This is normal behavior since you made a backup with the --clean option. In this case, the sql script tries to delete the database objects before they are created, but cannot find it.
You can ignore them or create a dump without the --clean option or with the additional --if-exists option (not sure if you would remove all errors).
source to share