PostgreSQL 9.0.13 doing a pg_restore but no evidence that disk space is being used

Question

I'm trying to restore a 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 over the pg-backup.sql file I'm trying to restore with this command

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

The pg-backup.sql file is 13GB. The pg-restore has been running for 4 hours, scrolling data up my screen the whole time. No errors.

But when I execute this statement from a psql session

SELECT pg_size_pretty(pg_database_size('salesDB'));

I get 5377 kB in size. WHAT? It should at least be 1GB by now. I'm totally lost. All this data is scrolling up my screen and I can't prove that it is going anywhere. No disk usage.

Help

Answer 1

Try it without the "-f" flag in the pg_restore command. Also, you might want to try creating the empty salesdb database and pass in "-d salesdb". Note that the db name will fold to lowercase unless it was created within double-quotes.

Added example steps to show that the db grows in size as the restore is running

-- 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 - syntax error the -f (output file) parameter is useless as far as I can tell. I needed to specify the file for pg_restore to consume without any flag, only as the last element in the command line. The -d salesdb parameter was needed. I have 16 cpus so I set -j 15, that seemed to be very helpful. my final command line was

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

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

It's growing like it should.