Postgresql Duplicate DB

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Postgresql Duplicate DB

Sathish Kumar
Hi All,

I would like to duplicate our existing db on the same server, what will be the faster way to achieve it.

DB size is around 300gb.
Reply | Threaded
Open this post in threaded view
|

RE: Postgresql Duplicate DB

Alvaro Aguayo Garcia-Rada

Hi. Not sure if the fastest, but the first that comes up to my mind is using pg_dump and psql. First you create your new database, then you run this(replacing as needed):

pg_dump OLDDB | psql NEWDB

Saludos,

Alvaro Aguayo
Jefe de Operaciones
Open Comb Systems E.I.R.L.

Oficina: (+51-1) 3377813 | Cel.: (+51) 995540103 | RPC: (+51) 954183248
Website: www.ocs.pe

Sent from my Sony Xperia™ smartphone



---- Sathish Kumar wrote ----


Hi All,

I would like to duplicate our existing db on the same server, what will be the faster way to achieve it.

DB size is around 300gb.
Reply | Threaded
Open this post in threaded view
|

Re: Postgresql Duplicate DB

Laurenz Albe
In reply to this post by Sathish Kumar
Sathish Kumar wrote:
> I would like to duplicate our existing db on the same server, what will be the faster way to achieve it.

If it is in the same database cluster, you can use

   CREATE DATABASE newdb TEMPLATE olddb;

Make sure nobody is connected to "olddb" when you do that.

If you want to clone a whole database cluster, you can run

   pg_basebackup -D /new/cluster/directory --wal-method=stream

and recover the new cluster with "restore_command = 'true'".

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com