Configuring Standby Server in PostgreSQL 9.3.3
<html><body><span style="font-family:Verdana; color:#000000; font-size:10pt;"><div>PostgreSQL version: 9.3.3<br>
Operating system: Redhat 6.4 Linux<br>
Description: </div><div><br></div><div>
We are in the process of setting up a standby server. Instead of restoring from a backup taken from primary server, <br>we want to shutdown the primary server, and then copy the primary database VM image to the standby server.<br><br>We want to do this because our database is 7TB and takes 4-5 to backup and 8-9 days to restore to the standby server.<br><br>Mock Primary :192.168.1.50<br>Mock standby:192.168.1.100<br><br>1.) Configure pg_hda.conf on primary<br><br># Allow the user "postgres" from host 192.168.1.100 to connect to the primary<br># as a replication standby if the user's password is correctly supplied.<br>#<br># TYPE DATABASE USER ADDRESS METHOD<br>host replication postgres 192.168.1.100/32 md5<br><br><br>2.) Configure recovery.conf on standby<br><br>standby_mode = 'on'<br>primary_conninfo = 'host=192.168.1.50 port=5432 user=postgres password=foopass'<br>restore_command = 'cp /path/to/archive/%f %p'<br>archive_cleanup_command = 'pg_archivecleanup /path/to/archive %r'<br><br>3.) re-start both servers<br><br>Question #1 Is this feasible?<br>Question #2 Have I missed critical steps?</div><div><br></div><div>Thanks<br></div></span></body></html>
On 4/2/2014 11:07 AM, fburgess@radiantblue.com wrote:
We want to do this because our database is 7TB and takes 4-5 to backup
and 8-9 days to restore to the standby server.
that might be true for pg_dump/pg_restore type backups, but the initial
copy made for streaming replication is a file system copy/clone.
how long does pg_basebackup take with your system operational?
--
john r pierce 37N 122W
somewhere on the middle of the left coast
<html><body><span style="font-family:Verdana; color:#000000; font-size:10pt;"><div>HI John, all of the backups we have taken were from the 24/7 uptime operational production servers. The 4-5 days duration is taken from these backups. Several months ago we restored one of these backups to another higher capacity database server on fiber channel storage in a different location, then we upgraded postgis from 1.5.8 to 2.1.1 and PostgreSQL from 9.1.6 to 9.3.3, that restore took 9-10 days to finish. We have another VM that has been set aside to be stood up asap as our standby server to this primary. Currently, this standby VM only has Linux 6.4 installed, no PostgreSQL or PostGIS. I was thinking if we can perform a VM clone, we wouldn't have to install PostgreSQL, Postgis, packages, etc., on the standby server.<br></div><div><br></div><div><br></div>
<blockquote id="replyBlockquote" webmail="1" style="border-left: 2px solid blue; margin-left: 8px; padding-left: 8px; font-size:10pt; color:black; font-family:verdana;">
<div id="wmQuoteWrapper">
-------- Original Message --------<br>
Subject: Re: [BUGS] Configuring Standby Server in PostgreSQL 9.3.3<br>
From: John R Pierce <<a href="mailto:pierce@hogranch.com">pierce@hogranch.com</a>><br>
Date: Wed, April 02, 2014 1:46 pm<br>
To: <a href="mailto:fburgess@radiantblue.com">fburgess@radiantblue.com</a>, <a href="mailto:pgsql-bugs@postgresql.org">pgsql-bugs@postgresql.org</a><br>
<br>
<div class="moz-cite-prefix">On 4/2/2014 11:07 AM, <a target="_blank" class="moz-txt-link-abbreviated" href="mailto:fburgess@radiantblue.com">fburgess@radiantblue.com</a> wrote:<br> </div> <blockquote cite="mid:20140402110749.5a830134ae84016b0174832fdc1a3173.28ee073a59.wbe@email11.secureserver.net" type="cite"><span style="font-family:Verdana; color:#000000; font-size:10pt;">We want to do this because our database is 7TB and takes 4-5 to backup and 8-9 days to restore to the standby server.</span></blockquote> <br> <br> <br> that might be true for pg_dump/pg_restore type backups, but the initial copy made for streaming replication is a file system copy/clone.<br> <br> how long does pg_basebackup take with your system operational?<br> <br> <pre class="moz-signature" cols="72">--
john r pierce 37N 122W
somewhere on the middle of the left coast</pre>
</div>
</blockquote></span></body></html>
Import Notes
Resolved by subject fallback
On 4/2/2014 2:58 PM, fburgess@radiantblue.com wrote:
HI John, all of the backups we have taken were from the 24/7 uptime
operational production servers. The 4-5 days duration is taken from
these backups. Several months ago we restored one of these backups to
another higher capacity database server on fiber channel storage in a
different location, then we upgraded postgis from 1.5.8 to 2.1.1 and
PostgreSQL from 9.1.6 to 9.3.3, that restore took 9-10 days to finish.
We have another VM that has been set aside to be stood up asap as our
standby server to this primary. Currently, this standby VM only has
Linux 6.4 installed, no PostgreSQL or PostGIS. I was thinking if we
can perform a VM clone, we wouldn't have to install PostgreSQL,
Postgis, packages, etc., on the standby server.
was that a file backup of the $PGDATA directory, or a database dump ?
you CANT start streaming replication with a database dump, as its not
the same timeline. you have to use a file system level backup.
quite frankly, dealing with a production database this large, I do
believe I am going to recommend you bring an experienced postgresql
consultant on board who's dealt extensively with large scale
replication, someone like 2ndQuadrant, or CommandPrompt, or PGExperts,
etc, and let them advise you.
--
john r pierce 37N 122W
somewhere on the middle of the left coast
On 04/02/2014 09:07 PM, fburgess@radiantblue.com wrote:
We are in the process of setting up a standby server. Instead of restoring from
a backup taken from primary server,
we want to shutdown the primary server, and then copy the primary database VM
image to the standby server.We want to do this because our database is 7TB and takes 4-5 to backup and 8-9
days to restore to the standby server.Mock Primary :192.168.1.50
Mock standby:192.168.1.1001.) Configure pg_hda.conf on primary
# Allow the user "postgres" from host 192.168.1.100 to connect to the primary
# as a replication standby if the user's password is correctly supplied.
#
# TYPE DATABASE USER ADDRESS METHOD
host replication postgres 192.168.1.100/32 md52.) Configure recovery.conf on standby
standby_mode = 'on'
primary_conninfo = 'host=192.168.1.50 port=5432 user=postgres password=foopass'
restore_command = 'cp /path/to/archive/%f %p'
archive_cleanup_command = 'pg_archivecleanup /path/to/archive %r'3.) re-start both servers
Question #1 Is this feasible?
Question #2 Have I missed critical steps?
The short answer is: yes, it's feasible. I think you got steps right.
But I agree with the other discussions that it really shouldn't take
days to do a filesystem-level backup/restore of a 7TB database, which is
what you need to set up a standby. There must be some misunderstanding
there.
- Heikki
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
have u already edit postgres.conf on primary?
-----
Michele Mariani
Database & Technology s.r.l.
tel: +39 02 89500080
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Configuring-Standby-Server-in-PostgreSQL-9-3-3-tp5798372p5798529.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
<html><body><span style="font-family:Verdana; color:#000000; font-size:10pt;"><div>No, Haven't set the server as primary yet. Spent yesterday installing PostgreSQL 9.3.3 and PostGIS 2.1.1 on the standby server. Now that's done, I think the next step is to run a backup from the yet to be setup Primary server.<br><br></div>
<blockquote id="replyBlockquote" webmail="1" style="border-left: 2px solid blue; margin-left: 8px; padding-left: 8px; font-size:10pt; color:black; font-family:verdana;">
<div id="wmQuoteWrapper">
-------- Original Message --------<br>
Subject: Re: [BUGS] Configuring Standby Server in PostgreSQL 9.3.3<br>
From: chileme88 <<a href="mailto:michele.mariani@databtech.com">michele.mariani@databtech.com</a>><br>
Date: Thu, April 03, 2014 8:31 am<br>
To: <a href="mailto:pgsql-bugs@postgresql.org">pgsql-bugs@postgresql.org</a><br>
<br>
have u already edit postgres.conf on primary?<br>
<br>
<br>
<br>
-----<br>
Michele Mariani<br>
Database & Technology s.r.l. <br>
tel: +39 02 89500080<br>
--<br>
View this message in context: <a href="http://postgresql.1045698.n5.nabble.com/Configuring-Standby-Server-in-PostgreSQL-9-3-3-tp5798372p5798529.html">http://postgresql.1045698.n5.nabble.com/Configuring-Standby-Server-in-PostgreSQL-9-3-3-tp5798372p5798529.html</a><br>
Sent from the PostgreSQL - bugs mailing list archive at <a href="http://Nabble.com">Nabble.com</a>.<br>
<br>
<br>
-- <br>
Sent via pgsql-bugs mailing list (<a href="mailto:pgsql-bugs@postgresql.org">pgsql-bugs@postgresql.org</a>)<br>
To make changes to your subscription:<br>
<a href="http://www.postgresql.org/mailpref/pgsql-bugs">http://www.postgresql.org/mailpref/pgsql-bugs</a><br>
</div>
</blockquote></span></body></html>
Import Notes
Resolved by subject fallback
Attachments:
Import Notes
Resolved by subject fallback
On 04/08/2014 01:25 AM, fburgess@radiantblue.com wrote:
Did you guys benchmark the basebackup utility? The master database will have to
remain online during this backup/restore process, since it is receiving real
time data feeds. Not sure which technique is better for our 7TB db.1.) Running from the slave. 1.2.3.4 is the ip of master database.
basebackup -D /u01/fiber/postgreSQL_data -F p -x stream -c fast -P -v -h 1.2.3.4
-p 5432 -U replication2.) compared to ...
psql -c "select pg_start_backup('initial_backup');"
rsync -cva --inplace --exclude=*pg_xlog* /u01/fiber/postgreSQL_data/
postgres@1.2.3.4:/u01/fiber/postgreSQL_data/
psql -c " select pg_stop_backup () ;"3.) or this ...
psql –c “select pg_start_backup(‘hot backup’)”
cp –pr /u01/fiber/postgreSQL_data 1.2.3.4:/u01/fiber/postgreSQL_data
psql –c “select pg_stop_backup(‘hot backup’)”
I bet the rsync or cp method is faster. Dunno how much, though, that
depends on what the bottleneck is; the network, or the disk, or
something else. You'll have to measure it yourself, to know how it is in
your environment.
- Heikki
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
On Apr 8, 2014, at 3:16 AM, Heikki Linnakangas <hlinnakangas@vmware.com> wrote:
On 04/08/2014 01:25 AM, fburgess@radiantblue.com wrote:
Did you guys benchmark the basebackup utility? The master database will have to
remain online during this backup/restore process, since it is receiving real
time data feeds. Not sure which technique is better for our 7TB db.1.) Running from the slave. 1.2.3.4 is the ip of master database.
basebackup -D /u01/fiber/postgreSQL_data -F p -x stream -c fast -P -v -h 1.2.3.4
-p 5432 -U replication2.) compared to ...
psql -c "select pg_start_backup('initial_backup');"
rsync -cva --inplace --exclude=*pg_xlog* /u01/fiber/postgreSQL_data/
postgres@1.2.3.4:/u01/fiber/postgreSQL_data/
psql -c " select pg_stop_backup () ;"3.) or this ...
psql –c “select pg_start_backup(‘hot backup’)”
cp –pr /u01/fiber/postgreSQL_data 1.2.3.4:/u01/fiber/postgreSQL_data
psql –c “select pg_stop_backup(‘hot backup’)”I bet the rsync or cp method is faster. Dunno how much, though, that depends on what the bottleneck is; the network, or the disk, or something else. You'll have to measure it yourself, to know how it is in your environment.
- Heikki
FWIW, I benchmarked a few methods here:
http://frosty-postgres.blogspot.com/2011/12/postgresql-base-backup-benchmark.html
admittedly, the base backup was much smaller, but I suspect you would see similar performance with a larger base backup.
You might also try running the rsync once, before issuing pg_start_backup() and then doing a normal base backup so that rsync can take advantage of partial file copy.
If you use rsync over ssh, make sure to use the arcfour cipher for best performance.
<html><body><span style="font-family:Verdana; color:#000000; font-size:10pt;"><div>Our standby server sits on a separate VM,. Should archive_mode = on be set only on the master and not the slave? My colleague is convinced that the archive log directory should sit on a shared drive that is accessible to both VM's (master and Slave)</div><div><br></div>
<blockquote id="replyBlockquote" webmail="1" style="border-left: 2px solid blue; margin-left: 8px; padding-left: 8px; font-size:10pt; color:black; font-family:verdana;">
<div id="wmQuoteWrapper">
-------- Original Message --------<br>
Subject: Re: [BUGS] Configuring Standby Server in PostgreSQL 9.3.3<br>
From: Jeff Frost <<a href="mailto:jeff@pgexperts.com">jeff@pgexperts.com</a>><br>
Date: Tue, April 08, 2014 9:39 am<br>
To: Heikki Linnakangas <<a href="mailto:hlinnakangas@vmware.com">hlinnakangas@vmware.com</a>><br>
Cc: <a href="mailto:fburgess@radiantblue.com">fburgess@radiantblue.com</a>, Michele <<a href="mailto:michele.mariani@databtech.com">michele.mariani@databtech.com</a>>,<br>
<a href="mailto:pgsql-bugs@postgresql.org">pgsql-bugs@postgresql.org</a><br>
<br>
<br>
On Apr 8, 2014, at 3:16 AM, Heikki Linnakangas <<a href="mailto:hlinnakangas@vmware.com">hlinnakangas@vmware.com</a>> wrote:<br>
<br>
> On 04/08/2014 01:25 AM, <a href="mailto:fburgess@radiantblue.com">fburgess@radiantblue.com</a> wrote:<br>
>> Did you guys benchmark the basebackup utility? The master database will have to<br>
>> remain online during this backup/restore process, since it is receiving real<br>
>> time data feeds. Not sure which technique is better for our 7TB db.<br>
>> <br>
>> 1.) Running from the slave. 1.2.3.4 is the ip of master database.<br>
>> <br>
>> basebackup -D /u01/fiber/postgreSQL_data -F p -x stream -c fast -P -v -h 1.2.3.4<br>
>> -p 5432 -U replication<br>
>> <br>
>> 2.) compared to ...<br>
>> <br>
>> psql -c "select pg_start_backup('initial_backup');"<br>
>> rsync -cva --inplace --exclude=*pg_xlog* /u01/fiber/postgreSQL_data/<br>
>> postgres@1.2.3.4:/u01/fiber/postgreSQL_data/<br>
>> psql -c " select pg_stop_backup () ;"<br>
>> <br>
>> 3.) or this ...<br>
>> <br>
>> psql –c “select pg_start_backup(‘hot backup’)”<br>
>> cp –pr /u01/fiber/postgreSQL_data 1.2.3.4:/u01/fiber/postgreSQL_data<br>
>> psql –c “select pg_stop_backup(‘hot backup’)”<br>
> <br>
> I bet the rsync or cp method is faster. Dunno how much, though, that depends on what the bottleneck is; the network, or the disk, or something else. You'll have to measure it yourself, to know how it is in your environment.<br>
> - Heikki<br>
<br>
FWIW, I benchmarked a few methods here:<br>
<br>
<a href="http://frosty-postgres.blogspot.com/2011/12/postgresql-base-backup-benchmark.html">http://frosty-postgres.blogspot.com/2011/12/postgresql-base-backup-benchmark.html</a><br>
<br>
admittedly, the base backup was much smaller, but I suspect you would see similar performance with a larger base backup.<br>
<br>
You might also try running the rsync once, before issuing pg_start_backup() and then doing a normal base backup so that rsync can take advantage of partial file copy.<br>
<br>
If you use rsync over ssh, make sure to use the arcfour cipher for best performance.<br>
<br>
</div>
</blockquote></span></body></html>
Import Notes
Resolved by subject fallback
<html><body><span style="font-family:Verdana; color:#000000; font-size:10pt;"><div>
Management requires that we implement a master server with two standby database servers. Is it recommended we keep the archives logs in separate directories in each VM, or that we have one directory on a shared drive that each of the three VM's can access? We would like to leverage the archive_cleanup_command = ‘pg_archivecleanup /sharedata/postgres_archive_master %r’ in our recovery.conf configuration on both standby servers.<br><br>-------- Original Message --------</div><blockquote id="replyBlockquote" webmail="1" style="border-left: 2px solid blue; margin-left: 8px; padding-left: 8px; font-size:10pt; color:black; font-family:verdana;"><div id="wmQuoteWrapper">
Subject: Re: [BUGS] Configuring Standby Server in PostgreSQL 9.3.3<br>
From: <<a href="mailto:fburgess@radiantblue.com">fburgess@radiantblue.com</a>><br>
Date: Tue, April 08, 2014 12:34 pm<br>
To: "Jeff Frost" <<a href="mailto:jeff@pgexperts.com">jeff@pgexperts.com</a>>, "Heikki Linnakangas"<br>
<<a href="mailto:hlinnakangas@vmware.com">hlinnakangas@vmware.com</a>><br>
Cc: "Michele" <<a href="mailto:michele.mariani@databtech.com">michele.mariani@databtech.com</a>>, <a href="mailto:pgsql-bugs@postgresql.org">pgsql-bugs@postgresql.org</a><br>
<br>
<span style="font-family:Verdana; color:#000000; font-size:10pt;"><div>Our standby server sits on a separate VM,. Should archive_mode = on be set only on the master and not the slave? My colleague is convinced that the archive log directory should sit on a shared drive that is accessible to both VM's (master and Slave)</div><div><br></div> <blockquote id="replyBlockquote" webmail="1" style="border-left: 2px solid blue; margin-left: 8px; padding-left: 8px; font-size:10pt; color:black; font-family:verdana;"> <div id="wmQuoteWrapper"> -------- Original Message --------<br> Subject: Re: [BUGS] Configuring Standby Server in PostgreSQL 9.3.3<br> From: Jeff Frost <<a target="_blank" href="mailto:jeff@pgexperts.com">jeff@pgexperts.com</a>><br> Date: Tue, April 08, 2014 9:39 am<br> To: Heikki Linnakangas <<a target="_blank" href="mailto:hlinnakangas@vmware.com">hlinnakangas@vmware.com</a>><br> Cc: <a target="_blank" href="mailto:fburgess@radiantblue.com">fburgess@radiantblue.com</a>, Michele <<a target="_blank" href="mailto:michele.mariani@databtech.com">michele.mariani@databtech.com</a>>,<br> <a target="_blank" href="mailto:pgsql-bugs@postgresql.org">pgsql-bugs@postgresql.org</a><br> <br> <br> On Apr 8, 2014, at 3:16 AM, Heikki Linnakangas <<a target="_blank" href="mailto:hlinnakangas@vmware.com">hlinnakangas@vmware.com</a>> wrote:<br> <br> > On 04/08/2014 01:25 AM, <a target="_blank" href="mailto:fburgess@radiantblue.com">fburgess@radiantblue.com</a> wrote:<br> >> Did you guys benchmark the basebackup utility? The master database will have to<br> >> remain online during this backup/restore process, since it is receiving real<br> >> time data feeds. Not sure which technique is better for our 7TB db.<br> >> <br> >> 1.) Running from the slave. 1.2.3.4 is the ip of master database.<br> >> <br> >> basebackup -D /u01/fiber/postgreSQL_data -F p -x stream -c fast -P -v -h 1.2.3.4<br> >> -p 5432 -U replication<br> >> <br> >> 2.) compared to ...<br> >> <br> >> psql -c "select pg_start_backup('initial_backup');"<br> >> rsync -cva --inplace --exclude=*pg_xlog* /u01/fiber/postgreSQL_data/<br> >> postgres@1.2.3.4:/u01/fiber/postgreSQL_data/<br> >> psql -c " select pg_stop_backup () ;"<br> >> <br> >> 3.) or this ...<br> >> <br> >> psql –c “select pg_start_backup(‘hot backup’)”<br> >> cp –pr /u01/fiber/postgreSQL_data 1.2.3.4:/u01/fiber/postgreSQL_data<br> >> psql –c “select pg_stop_backup(‘hot backup’)”<br> > <br> > I bet the rsync or cp method is faster. Dunno how much, though, that depends on what the bottleneck is; the network, or the disk, or something else. You'll have to measure it yourself, to know how it is in your environment.<br> > - Heikki<br> <br> FWIW, I benchmarked a few methods here:<br> <br> <a target="_blank" href="http://frosty-postgres.blogspot.com/2011/12/postgresql-base-backup-benchmark.html">http://frosty-postgres.blogspot.com/2011/12/postgresql-base-backup-benchmark.html</a><br> <br> admittedly, the base backup was much smaller, but I suspect you would see similar performance with a larger base backup.<br> <br> You might also try running the rsync once, before issuing pg_start_backup() and then doing a normal base backup so that rsync can take advantage of partial file copy.<br> <br> If you use rsync over ssh, make sure to use the arcfour cipher for best performance.<br> <br> </div> </blockquote></span>
</div>
</blockquote></span></body></html>
Import Notes
Resolved by subject fallback