A faster way to copy a postgresql database (or the best way)

I did a pg_dump of a database and am now trying to install the resulting .sql file on to another server.

I'm using the following command.

psql -f databasedump.sql

I initiated the database install earlier today and now 7 hours later the database is still being populated. I don't know if this his how long it is supposed to take, but I continue to monitor it, so far I've seen over 12 millon inserts and counting. I suspect there's a faster way to do this.

Create your dumps with

pg_dump -Fc -Z 9  --file=file.dump myDb

Fc > Output a custom archive suitable for input into pg_restore. This is the most flexible format in that it allows reordering of loading data as well as object definitions. This format is also compressed by default.

Z 9: --compress=0..9
> Specify the compression level to use. Zero means no compression. For the custom archive format, this specifies compression of individual table-data segments, and the default is to compress at a moderate level. For plain text output, setting a nonzero compression level causes the entire output file to be compressed, as though it had been fed through gzip; but the default is not to compress. The tar archive format currently does not support compression at all.

and restore it with

pg_restore -Fc -j 8  file.dump

-j: --jobs=number-of-jobs
> Run the most time-consuming parts of pg_restore — those which load data, create indexes, or create constraints — using multiple concurrent jobs. This option can dramatically reduce the time to restore a large database to a server running on a multiprocessor machine. > > Each job is one process or one thread, depending on the operating system, and uses a separate connection to the server. > > The optimal value for this option depends on the hardware setup of the server, of the client, and of the network. Factors include the number of CPU cores and the disk setup. A good place to start is the number of CPU cores on the server, but values larger than that can also lead to faster restore times in many cases. Of course, values that are too high will lead to decreased performance because of thrashing. > > Only the custom and directory archive formats are supported with this option. The input must be a regular file or directory (not, for example, a pipe). This option is ignored when emitting a script rather than connecting directly to a database server. Also, multiple jobs cannot be used together with the option --single-transaction.

Links:

pg_dump

pg_restore

Improve pg dump&restore

PG_DUMP | always use format directory with -j option

time pg_dump -j 8 -Fd -f /tmp/newout.dir fsdcm_external

PG_RESTORE | always use tuning for postgres.conf with format directory With -j option

work_mem = 32MB
shared_buffers = 4GB
maintenance_work_mem = 2GB
full_page_writes = off
autovacuum = off
wal_buffers = -1

time pg_restore -j 8 --format=d -C -d postgres /tmp/newout.dir/`

For more info

<https://gitlab.com/yanar/Tuning/wikis/improve-pg-dump&restore>

Why are you producing a raw .sql dump? The opening description of pg_dump recommends the "custom" format -Fc.

Then you can use pg_restore which will restore your data (or selected parts of it). There is a "number of jobs" option -j which can use multiple cores (assuming your disks aren't already the limiting factor). In most cases, on a modern machine you can expect at least some gains from this.

Now you say "I don't know how long this is supposed to take". Well, until you've done a few restores you won't know. Do monitor what your system is doing and whether you are limited by cpu or disk I/O.

Finally, the configuration settings you want for restoring a database are not those you want to run it. A couple of useful starters:

1. Increase maintenance_work_mem so you can build indexes in larger chunks
2. Turn off fsync during the restore. If your machine crashes, you'll start from scratch again anyway.

Do remember to reset them after the restore though.

The usage of pg_dump is generally recommended to be paired with pg_restore, instead of psql. This method can be split among cores to speed up the loading process by passing the --jobs flag as such:

$ pg_restore --jobs=8 dump.sql

Postgres themselves have a guide on bulk loading of data.

I also would recommend heavily tuning your postgresql.conf configuration file and set appropriately high values for the maintenance_work_mem and checkpoint_segments values; higher values on these may dramatically increase your write performance.