Обсуждение: Enquiry about setting Up PostgreSQL 8.4 PIRT
Dear all,
We are using Postgres 8.4 in Windows environment in our setting. I have been tasked with setting up a Postgres PITR. Having gone through the documentation, I have bumped into certain issues that hasn’t made my setup sucessful. What I have done is setup 2 servers. First, I modified main server’s postgresql.conf by enabling archive_mode and puttiing the command to perform the actual archiving, and this is all good.
I have further created a windows scheduled task that copies the archived WAL files to a remote server(the Postgres slave server). I have also installed Grsync on the Master server to help me copy files from main server to slave even as the main is being used, as previous copy command on Windows cmd always displayed errors of along the lines of “cannot access file, file being used by another process”.
After running select pg_start_backup('Master_Base_Backup); on the main server, I launch Grsync to copy the main server’s data dir to the slave server. When this process completes, I run
select pg_stop_backup(); on the main server again.
I then copy all the data from Master server to the slave’s data dir (deleting all files in pg_xlog and postmaster.pid and modifying postmaster.conf appropriately). I have created the needed recovery.conf (as mentioned on the documentation). On restarting the slave, the server restarts and hopefully restores the WAL files, I say hopefully because, NO more WAL files are copied from the archive directory to pg_xlog on the slave. And on creating a table in the Master server, it is not reflected on the slave server.
Kindly inform me where I might have erred. Thanks in advance for your support and feedback.
Kind regards,
Caesar Olima
Kilifi, Kenya
______________________________________________________________________
This e-mail contains information which is confidential. It is intended only for the use of the named recipient. If you have received this e-mail in error, please let us know by replying to the sender, and immediately delete it from your system. Please note, that in these circumstances, the use, disclosure, distribution or copying of this information is strictly prohibited. KEMRI-Wellcome Trust Programme cannot accept any responsibility for the accuracy or completeness of this message as it has been transmitted over a public network. Although the Programme has taken reasonable precautions to ensure no viruses are present in emails, it cannot accept responsibility for any loss or damage arising from the use of the email or attachments. Any views expressed in this message are those of the individual sender, except where the sender specifically states them to be the views of KEMRI-Wellcome Trust Programme.
______________________________________________________________________
On Friday, January 22, 2016 03:39:37 PM Caesar Olima wrote:
> Dear all,
>
> We are using Postgres 8.4 in Windows environment in our setting. I have been
> tasked with setting up a Postgres PITR. Having gone through the
> documentation, I have bumped into certain issues that hasn't made my setup
> sucessful. What I have done is setup 2 servers. First, I modified main
> server's postgresql.conf by enabling archive_mode and puttiing the command
> to perform the actual archiving, and this is all good.
>
> I have further created a windows scheduled task that copies the archived WAL
> files to a remote server(the Postgres slave server). I have also installed
> Grsync on the Master server to help me copy files from main server to slave
> even as the main is being used, as previous copy command on Windows cmd
> always displayed errors of along the lines of "cannot access file, file
> being used by another process".
>
> After running select pg_start_backup('Master_Base_Backup); on the main
> server, I launch Grsync to copy the main server's data dir to the slave
> server. When this process completes, I run select pg_stop_backup(); on the
> main server again.
>
> I then copy all the data from Master server to the slave's data dir
> (deleting all files in pg_xlog and postmaster.pid and modifying
> postmaster.conf appropriately). I have created the needed recovery.conf (as
> mentioned on the documentation). On restarting the slave, the server
> restarts and hopefully restores the WAL files, I say hopefully because, NO
> more WAL files are copied from the archive directory to pg_xlog on the
> slave. And on creating a table in the Master server, it is not reflected on
> the slave server.
You seem to be confusing two concepts.
A PITR backup is a way to generate a good backup of a running database that
can later be restored to a consistent state for data recovery purposes.
Doing that restoration does not automatically setup ongoing replication.
You can use a PITR base backup as the starting point for replication, but you
also need to do other things on the slave - at a minimum setting standby_mode
= on, and setting trigger_file, both in recovery.conf, in order to get that
working. If you want streaming replication, and you probably do, there are
additional changes on both the master and slave needed to support that.
You also seem to indicate that you're doing two separate copies of the master
to the slave, which is probably wrong, but that might just be unclear
communication.
Unfortunately I can't help you specifically with the commands needed to make
this work on Windows, but hopefully if you separate the restore concept and
the replication concepts you can figure out where things went wrong.
Вложения
On Fri, Jan 22, 2016 at 9:39 AM, Caesar Olima <COlima@kemri-wellcome.org> wrote: > We are using Postgres 8.4 in Windows environment I sure hope that is a typo. If not, you should upgrade to (the latest minor release of) PostgreSQL 9.4 or 9.5 before worrying about anything else. 8.4 was introduced in July of 2009 and went out of support in July of 2014. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company