Clone database using rsync?

On Tue, Nov 5, 2013 at 3:11 PM, sparikh <sparikh@ecotality.com> wrote:

I need to clone production database to development server ? What is the
best
and simplest way to achieve that? Both my production and development
postgres versions are same. Will copy over data folder using rsync work?

How are you currently backing up your production database? I usually
piggy-back off of that method to set up the clone. It kills two birds with
one stone, you both get your clone for dev, and you exercise your disaster
recovery procedure.

Cheers,

Jeff

On 11/5/2013 3:11 PM, sparikh wrote:

I need to clone production database to development server ? What is the best
and simplest way to achieve that? Both my production and development
postgres versions are same. Will copy over data folder using rsync work?

if the database isn't gigenormous, on the new ssytem, I use the
following, while logged on as postgres...

1. manually create any roles and tablespaces the database requires
2. manually create the new database owned by the proper role
3. pg_dump -h oldhost -d dbname -Fc | pg_restore -d newdb

copying the data folder only works if you either stop the postgres
server, or invoke pg_start_backup(), or use pg_basebackup [1]http://www.postgresql.org/docs/current/static/app-pgbasebackup.html

[1]: http://www.postgresql.org/docs/current/static/app-pgbasebackup.html

--
john r pierce 37N 122W
somewhere on the middle of the left coast

Copying the data folder should work as long as you stop the postgres
service on the production server before starting the copy and don't start
it up again until the copy finishes. pg_dump and pg_restore (look them up
in the online docs) will get the job done without you having to take the
production server offline.

If you go with the folder copy and your installation has postgresql.conf,
pg_hba.conf and so on in your data folder, you'll probably want to edit
them after the copy - more logging, different security etc.

Matt

I need to clone production database to development server ? What is the
best
and simplest way to achieve that? Both my production and development
postgres versions are same. Will copy over data folder using rsync work?

Thanks in advance.

--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Clone-database-using-rsync-tp5777141.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Thanks Jeff for your quick response.

I inherited this system and they had cron job which uses pg_dump for back
up. I recently used to rsync to bring back my hot standby when it was out of
sync and offline for few days because of space issue. That is when the
thought that I might be able to use rsync to clone database.

If I understood your reply correctly, you are currently using rsync for both
clone and backup. Is that correct?

Thanks Again.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Clone-database-using-rsync-tp5777141p5777150.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 11/05/2013 03:41 PM, matt@byrney.com wrote:

Copying the data folder should work as long as you stop the postgres
service on the production server before starting the copy and don't start
it up again until the copy finishes. pg_dump and pg_restore (look them up
in the online docs) will get the job done without you having to take the
production server offline.

If you go with the folder copy and your installation has postgresql.conf,
pg_hba.conf and so on in your data folder, you'll probably want to edit
them after the copy - more logging, different security etc.

OP did not say what version they where on but pg_basebackup could be an
option, no shutdown necessary either:

http://www.postgresql.org/docs/9.3/interactive/app-pgbasebackup.html

Matt

I need to clone production database to development server ? What is the
best
and simplest way to achieve that? Both my production and development
postgres versions are same. Will copy over data folder using rsync work?

Thanks in advance.

--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Clone-database-using-rsync-tp5777141.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Adrian Klaver
adrian.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Tue, Nov 5, 2013 at 3:49 PM, sparikh <sparikh@carcharging.com> wrote:

Thanks Jeff for your quick response.

I inherited this system and they had cron job which uses pg_dump for back
up. I recently used to rsync to bring back my hot standby when it was out
of
sync and offline for few days because of space issue. That is when the
thought that I might be able to use rsync to clone database.

Ah, I see. Yes, you can't use pg_dump to seed a standby, so if really want
to have a pg_dump for backup, then you will need to have two different
methods.

If I understood your reply correctly, you are currently using rsync for
both
clone and backup. Is that correct?

No, I use pg_basebackup and wal archiving/recovery for both backup, and for
cloning dev/test servers. I'm pondering whether I should also take pg_dump
occasionally so that I have a platform-independent secondary backup.

But I'm a big fan of practicing your restoration procedure, so if you want
to keep using pg_dump as your backup, I think I'd also use that dump to
make clones, unless there is a good reason not to.

Cheers,

Jeff

Just want to give you some idea for your reference.

If you are using zfs, you will be gaining lots of advantages. A simple
clone and send command from zfs will help you big time.

Check it out: http://docs.oracle.com/cd/E18752_01/html/819-5461/docinfo.html

On Tue, Nov 5, 2013 at 6:11 PM, sparikh <sparikh@ecotality.com> wrote:

I need to clone production database to development server ? What is the
best
and simplest way to achieve that? Both my production and development
postgres versions are same. Will copy over data folder using rsync work?

Thanks in advance.

--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Clone-database-using-rsync-tp5777141.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--

- *Laurentius Purba*
- SproutLoud Media Networks, LLC.
- lpurba@sproutloud.com
- www.sproutloud.com
- •

- [image: Facebook] <http://www.facebook.com/SproutLoud&gt; [image:
Twitter]<http://twitter.com/sproutloud&gt;
[image: Google+] <http://plus.google.com/100508252462681981365&gt; [image:
LinkedIn] <http://www.linkedin.com/company/440456?trk=tyah&gt; [image:
YouTube] <http://www.youtube.com/user/sproutcms&gt; [image:
Pinterest]<http://pinterest.com/sproutloud/&gt;
[image: Blog RSS] <http://blog.sproutloud.com/&gt; [image: SproutLoud
Newsletter] <http://market.sproutloud.com/Newsletter-signup.html&gt;
-