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

Hatti

On a side note, congrats for keeping up with the current patch release. I see too many 9.0.4's around here. – Berard

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

Cottonmouth

What you're saying it that I should see the db size growing every time I execute the pg_database_size function during the restore? and until I see that I don't have it correct? – Hatti

@WadeJernigan, I've edited my answer to show that the db grows during the pg_restore. – Cottonmouth

RESOLVED - syntax error the -f 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 – Hatti

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.

Hatti

I'm not totally familiar with how this forum works but point to bma. He was right, thanks. – Hatti

I think you just "accept" the answer you chose as the correct one. – Cottonmouth

Answer 3

Looks to me like you've been telling pg_restore to print the dump's contents to the display, not restore it to the database. Did you specify a --dbname?

Personally I think pg_restore's command line syntax isn't particularly intuitive, and if I ever get time it's one of the things I'd like to try to improve in Pg.

Berard

you cannot use --dbname if you specify a -f (file name) but yes, it does seem that I was writing only to the screen. So I removed the --verbose switch. Now I have nothing on the screen for the past 14 hours and no proof that the database is growing either. grrr I need a way to prove the data is going in to the database. I'll try some select statements. I wish it would report a growing size. – Hatti