Thread: [SQL] How to duplicate postgres 9.4 database
Hi All I want to duplicate the existing database , after reading documentation I used below command which just created database, but did not create data CREATE DATABASE standby TEMPLATE template0; my database is 35gb so dump and psql will take longer hours is there an fastest way to copy database on same server , so that I can just modify pointing to the database and connect to the stanby one to do any testing ? -- View this message in context: http://www.postgresql-archive.org/How-to-duplicate-postgres-9-4-database-tp5972442.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
-----Original Message----- From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of srilinux Sent: Friday, July 21, 2017 3:29 PM To: pgsql-sql@postgresql.org Subject: [SQL] How to duplicate postgres 9.4 database Hi All I want to duplicate the existing database , after reading documentation I used below command which just created database,but did not create data CREATE DATABASE standby TEMPLATE template0; my database is 35gb so dump and psql will take longer hours is there an fastest way to copy database on same server , so that I can just modify pointing to the database and connectto the stanby one to do any testing ? __________________________________________________________________________________________________________ As a TEMPLATE you should use the db that you want to copy, let's call it "source_db": CREATE DATABASE standby TEMPLATE source_db; Be aware, that when this statement runs there should be no user connections to source_db. Regards, Igor Neyman
Thank you very much
I will try it , how much time will it take for 35gb database ? is this method suggest able for a 35gb database ?On Fri, Jul 21, 2017 at 4:08 PM, Igor Neyman [via PostgreSQL] <[hidden email]> wrote:
-----Original Message-----
From: [hidden email] [mailto:[hidden email]] On Behalf Of srilinux
Sent: Friday, July 21, 2017 3:29 PM--To: [hidden email]
Subject: [SQL] How to duplicate postgres 9.4 database
Hi All
I want to duplicate the existing database , after reading documentation I used below command which just created database, but did not create data
CREATE DATABASE standby TEMPLATE template0;
my database is 35gb so dump and psql will take longer hours
is there an fastest way to copy database on same server , so that I can just modify pointing to the database and connect to the stanby one to do any testing ?
____________________________________________________________ ______________________________ ________________
As a TEMPLATE you should use the db that you want to copy, let's call it "source_db":
CREATE DATABASE standby TEMPLATE source_db;
Be aware, that when this statement runs there should be no user connections to source_db.
Regards,
Igor Neyman
Sent via pgsql-sql mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql If you reply to this email, your message will be added to the discussion below:http://www.postgresql-archive.org/How-to-duplicate-postgres- 9-4-database- tp5972442p5972447.html
View this message in context: Re: How to duplicate postgres 9.4 database
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
On Sat, Jul 22, 2017 at 4:43 PM, srilinux <srilinux09@gmail.com> wrote: > Thank you very much > > > I will try it , how much time will it take for 35gb database ? is this > method suggest able for a 35gb database ? Very much depends on your server. I've had some that could copy that much over in a few minutes, some that might take most of an hour. But, generally speaking creating a db from a template is about as fast as it gets.