PostgreSQL 9.0.13 does pg_restore but does not show that disk space is being used

Question

I am trying to restore pg_dump taken with this command from another server.

sudo -u postgres pg_dump --verbose --format=custom --file=pg-backup.sql -U postgres salesDB 

After I copied the pg-backup.sql file, I try to restore using this command

 sudo -u postgres pg_restore --verbose --jobs=`nproc` -f pg-backup.sql 

The pg-backup.sql file is 13 GB in size. Pg-restore works for 4 hours, scrolling through the data on my screen all the time. No mistakes.

But when I execute this statement from the psql session

 SELECT pg_size_pretty(pg_database_size('salesDB')); 

I get a size of 5377 kB. WHAT? It should at least be 1 GB by now. I am completely lost. All this data scrolls my screen, and I can’t prove that it goes anywhere. No disk usage.

reference

Answer 1

Try this without the “-f” flag on the pg_restore command. Alternatively, you can try creating an empty salesdb database and go to "-d salesdb". Note that the db name will be added to lowercase if it was not created in double quotes.

Added example steps showing that the db size increases as recovery is performed

 -- sample pg_dump command pg_dump -f testdb.out -Fc src_test_db -- create the db to restore into createdb sometestdb -- restore with 4 parallel jobs, from the "testdb.out" file, into the db "sometestdb" time pg_restore --dbname=sometestdb --jobs=4 testdb.out -- In another window, every few seconds, you can see the db growing psql -d postgres -c "select pg_size_pretty(pg_database_size('sometestdb'))" pg_size_pretty ---------------- 4920 MB psql -d postgres -c "select pg_size_pretty(pg_database_size('sometestdb'))" pg_size_pretty ---------------- 4920 MB psql -d postgres -c "select pg_size_pretty(pg_database_size('sometestdb'))" pg_size_pretty ---------------- 5028 MB psql -d postgres -c "select pg_size_pretty(pg_database_size('sometestdb'))" pg_size_pretty ---------------- 5371 MB 

Answer 2

RESOLVED is a syntax error: the -f option ( output ) is useless as far as I can tell. I needed to specify a file for pg_restore to use without any flag, only as the last item on the command line. The -d salesdb . I have 16 processors, so I installed -j 15 , which seemed very useful. my last command line was

 sudo -u postgres pg_restore -d salesdb --jobs=15 backup10.sql 

Then I get very fast size increments using the pg_database_size function.

It is growing as it should.

Answer 3

It looks like you told pg_restore print the dump contents on the display, and not restore it to the database. Did you specify --dbname ?

Personally, I think the pg_restore command line pg_restore not particularly intuitive, and if I ever get the time, this is one of the things I would like to try improving in Pg.