0

I have a database which is 160GB in size, and has about 500,000 tables. I ran a pg_dump yesterday using:

pg_dump -U mps db_name -f db_name.sql

The process seems to be running for more than 24 hours now, and i dont know how much time it will take to complete. There are no errors, and the dump is running smoothly. Is there a way to approximate the time of dump? What would be the size of the resulting dump file?

Also, if dump took so much time, how much time would pg_restore take?

Edits:

pg_dump --version 
pg_dump (PostgreSQL) 8.4.20

select version()
8.4.20 PostgreSQL 8.4.20 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.4.7 2 0120313 (Red Hat 4.4.7-4), 64-bit

Also, i noticed that the resulting backup file does not show any disk space

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
user3455531
  • 775
  • 1
  • 14
  • 28
  • There were some improvements made to `pg_dump` for databases with huge numbers of tables/schemas recently. What PostgreSQL version is this? Show the output of `pg_dump --version` and of `SELECT version()` please. – Craig Ringer Sep 19 '14 at 05:39
  • 4
    Also, *half a million tables*? Why?!? Sharding by schema gone wild? – Craig Ringer Sep 19 '14 at 05:40
  • See closely related: http://dba.stackexchange.com/q/29699/7788 , http://serverfault.com/q/56323/102814, http://dba.stackexchange.com/q/51742/7788, http://postgresql.1045698.n5.nabble.com/pg-dump-and-thousands-of-schemas-td5709766.html, many more. – Craig Ringer Sep 19 '14 at 05:42
  • 1
    What worries me about this is that you have 500,000 tables and *this is the first time you've run `pg_dump`*. I will hope that you've just had a really good PITR/archiving and snapshot based system in place, rather than that you've simply not been taking backups. – Craig Ringer Sep 19 '14 at 05:43
  • I have been not been manually taking backups with pg_dump, but there have been times when pg_dump has been running in background. – user3455531 Sep 19 '14 at 05:46
  • and would there be a reason for it to now show any disk usage? – user3455531 Sep 19 '14 at 05:52
  • I'd say `pg_dump` will be using 100% cpu while it's sorting the table list to determine dependencies and foreign key relationships for that many tables. It might also be taking locks. – Craig Ringer Sep 19 '14 at 05:54
  • If your dump file isn't using any disk space, how do you know it's running smoothly? – Gabe Sep 19 '14 at 05:57
  • It is using disk space, but i cant see how much exactly, as in it does not show anything – user3455531 Sep 19 '14 at 06:01
  • Well, a good starting point would be planning an update to PostgreSQL 9.3, which has a bunch of performance improvements for `pg_dump` with large schema/table counts. – Craig Ringer Sep 19 '14 at 06:04
  • i already upgraded the version in the new server. just transfering data from the previous server – user3455531 Sep 19 '14 at 06:07
  • You are supposed to run the new version of pg_dump to dump out the contents of the old server. (Not that this will necessarily cure the problem, but it won't hurt and might help) – jjanes Sep 19 '14 at 15:53

0 Answers0