Restore database from pg_dump

Question

I had non-empty db and made backup it by:

pg_dump myBase > backup.sql

Then I must to delete db and now I am trying to restore it from backup.sql. So first I create database by:

create database myBase

grant privileges from psql

grant all privileges on database "myBase" to myUser

and then trying to recover from pg_dump by:

pg_dump myBase < backup.sql

And I didn't get any errors, but when I check tables by \dt it's totally empty. I have also tried restoring with:

psql -h localhost -d myBase-U myUser-f backup.sql (no error, but no tables too)

psql myBase < backup.sql (like above)

I have a little expieriences with dbs so I assume it should be something obvious for more experienced user.

how do you check if the dp is empty? `psql myBase -c "\dt"`?.. — Vao Tsun, Jul 05 '17 at 12:37
so before you drop db you have a list and after restoring same command shows no relations found?.. — Vao Tsun, Jul 05 '17 at 12:52
Yes, before drop "\dt" shows list of tables, and after restore I have "no relations found". — Piotr Wu, Jul 05 '17 at 13:02
cant reproduce. see the answer. no idea why you have no errors and no relations. I mean many possible failure points, but can't see how to guess — Vao Tsun, Jul 05 '17 at 13:21
@JGH It has abaout 1500 lines, so I am not sure in 100% everything is ok, but I check couple spots and looks fine, with my tables, columns and data. But I didn't use any validator, is there any? — Piotr Wu, Jul 05 '17 at 14:15
just in case `psql -h localhost -d myBase-U myUser -f backup.sql -X` does not change anything?.. — Vao Tsun, Jul 05 '17 at 14:34

score 1 · Answer 1 · answered Jul 05 '17 at 13:21

1

Can't reproduce the issue:

postgres=# create database so;
CREATE DATABASE
postgres=# \c so
You are now connected to database "so" as user "postgres".
so=# create table so(i int);
CREATE TABLE
so=# insert into so values (1);
INSERT 0 1
so=# \! pg_dump so > /tmp/so.sql
so=# \c postgres
You are now connected to database "postgres" as user "postgres".
postgres=# drop database so;
DROP DATABASE
postgres=# create database so;
CREATE DATABASE
postgres=# \c so
You are now connected to database "so" as user "postgres".
so=# \dt
No relations found.
so=# \! psql so -f /tmp/so.sql
SET
SET
SET
SET
SET
SET
CREATE EXTENSION
COMMENT
SET
SET
SET
CREATE TABLE
ALTER TABLE
REVOKE
REVOKE
GRANT
GRANT
so=# \dt
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 public | so   | table | postgres
(1 row)

answered Jul 05 '17 at 13:21

Vao Tsun

47,234
13
100
132

I follow your instructions and example for "SO" work without problems, but my case still doesn't work. What I see is difference on restoring db line "-f /tmp/so.sql". You example produces couple lines of what is currently happening, and mine not even one. Maybe my dump is broken? – Piotr Wu Jul 05 '17 at 14:13
if you have an error in statements it will give error to stdout... of course you can add `\o some_file` to the first line of sql script and then psql will send all output to some_file, but you said you did not modify it?.. neither you used `-o` switch - right?.. – Vao Tsun Jul 05 '17 at 14:27
BTW dump can't be broken. It works on Windows.I create new database, user, grant privileges, restore data and locally it works. So it just doesn't work on linux for some reason – Piotr Wu Jul 05 '17 at 14:59

score 0 · Answer 2 · answered Dec 17 '18 at 22:46

Got the same problem, found the solution here: Unable to restore psql database from pg_dump with a different username

pg_dump dbname -O -x > backupname.sql

-O <-- No owner Do not output commands to set ownership of objects to match the original database

-x <-- No privileges Prevent dumping of access privileges (grant/revoke commands)

Restore database from pg_dump

2 Answers2