4

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?

Improve this question

2 Answers 2

Reset to default
3

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

You can't copy a template, as Laurenz says. The reason is that it needs to be integrated into postgres's shared catalogs like pg_database, etc, and because it shares database-wide resources like the transaction log (pg_clog).

In theory you actually could copy a template from another machine if all tuples were frozen by VACUUM FREEZE. But you'd have to do some manual setup to integrate the copied template, and it'd be bug prone and risky. You'd need very good knowledge of postgres internals.

It's really not worth it.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.