postgres, create database from template on external server

Question

I have two servers with same version of postgres(9.4). I would like to make fast clone database (backup and restore). As i understand "create database xxx from template" is fastest way to make copy of database.

How can i do cloning from one server to another? May i use dblink for this task and how?

I know solution which use pg_dump and pg_restore(psql), but i think it is too slow for me. For example, 5gb database was restored around 12 minutes(with -j option in pg_restore) and backuped around 2 minutes.

Another way, may be, Can i copy some postgres files(/var/lib/postgresql/*) to another server, and run postgres there? How to find this files?

Answer 1

You cannot use a database from a different database cluster (on the same or a different machine) as a template to create a new database, so that's out.

The only existing way to copy a database from one cluster to the other is pg_dump/pg_restore.

A physical copy is the fastest way, but you cannot just copy individual files or databases from one database cluster to another.

If you don't mind copying the whole database cluster, there is the option to stop and remove the target database cluster, perform pg_basebackup -X stream to create a physical backup of the source database cluster, edit the configuration as necessary and start the new copy.

If pg_basebackup is too slow for you, you can perform an online backup “by hand” with pg_start_backup and pg_stop_backup and copy the files inbetween with the fastest technology you have.

If it is no problem to stop the source database cluster, you don't have to mess with recovery and you will be even faster.