Postgres Pro
Downloads
Home   >   mailing lists

Thread: Issue with PostgreSQL replication and PITR

Issue with PostgreSQL replication and PITR

From
Amee Sankhesara - Quipment India
Date:

Quipment Logo

Hello,

 

We have a windows(2016) setup for PostgreSQL(Version 9.6.5).

 

We have setup replication and PITR on this PostgreSQL server and wal files for replication and PITR we are storing on the another server which is shared with primary and secondary server.

 

For doing some OS migration activity for the PostgreSQL database servers

We created two new replication server and promoted to new master server for the same

 

During that activity one .partial file is created on master server and after that wal files are not copied in shared folder from which secondary server take it and restore it. All Wal files after .partial file are stored in pg_xlog folder of master data so it is increasing size of data directory of master server.

 

Wal files are not copying in shared location of master and secondary server but still replication is running and on secondary server data is up to date.

 

In logs getting only below warning :

Warning : The failed archive command was: copy "D:\PostgreSQL\9.6\data\pg_xlog\0000000100000A88000000F8.partial" "\\10.0.0.35\Archive_Replication\0000000100000A88000000F8.partial" | copy "D:\PostgreSQL\9.6\data\pg_xlog\0000000100000A88000000F8.partial" "\\10.0.0.35\Archive_PITR\0000000100000A88000000F8.partial"

 

Can’t figure out that in shared folder wal files are not copied but still secondary server is running in sync with master server.

 

What could be reason behind this?

 

And how to remove this .partial file from pg_xlog safely and how to start copying wal files from pg_xlog of master server to shared location of master and secondary server ?

 

Thanks,

Amee Sankhesara
Database Developer

R&D .Net Department

T / +91 (0) 94295 45266
amee.sankhesara@ / www.quipment.in

 

QTwee Group BV
KvK Arnhem 60858001
BTW NL854090721B01
mail@ / www.quipment.nl

Hoofdkantoor Nijmegen
Kerkenbos 1236 C
6546 BE Nijmegen

Tel.

+31 (0)24 372 47 00

Fax.

+31 (0)24 372 47 07

Quipment India
903-904 Landmark,
Beside Titanium City Centre,
Anandnagar Road, 100ft Ring Road,
Ahmedabad
Gujarat - 380015 - India
Tel. +91 (0) 79 4009 7847

Disclaimer

This e-mail message (including any attachment) is intended only for the personal use of the recipient(s) named above. This message is confidential and may be legally privileged. If you are not an intended recipient, you may not review, copy or distribute this message. If you have received this communication in error, please notify us immediately by e-mail and delete the original message.

 

Attachment

Re: Issue with PostgreSQL replication and PITR

From
Laurenz Albe
Date:
Amee Sankhesara - Quipment India wrote:
> We have setup replication and PITR on this PostgreSQL server and wal files
> for replication and PITR we are storing on the another server which is
> shared with primary and secondary server.
>  
> For doing some OS migration activity for the PostgreSQL database servers
> We created two new replication server and promoted to new master server for the same
>  
> During that activity one .partial file is created on master server and after
> that wal files are not copied in shared folder from which secondary server
> take it and restore it. All Wal files after .partial file are stored in pg_xlog
> folder of master data so it is increasing size of data directory of master server.
>  
> Wal files are not copying in shared location of master and secondary server but
> still replication is running and on secondary server data is up to date.
>  
> In logs getting only below warning :
> Warning : The failed archive command was:
>   copy "D:\PostgreSQL\9.6\data\pg_xlog\0000000100000A88000000F8.partial"
>   "\\10.0.0.35\Archive_Replication\0000000100000A88000000F8.partial"
>   | copy "D:\PostgreSQL\9.6\data\pg_xlog\0000000100000A88000000F8.partial"
>   "\\10.0.0.35\Archive_PITR\0000000100000A88000000F8.partial"
>  
> Can’t figure out that in shared folder wal files are not copied but still
> secondary server is running in sync with master server.
>  
> What could be reason behind this?

If archiving is not working and the standby still can replicat, I would assume
that streaming replication is configured.

Is "primary_conninfo" defined in "recovery.conf"?
Are there WAL sender and WAL receiver processes?
 
> And how to remove this .partial file from pg_xlog safely and how to start
> copying wal files from pg_xlog of master server to shared location of master
> and secondary server ?

You should try to figure out why your "archive_command" fails; perhaps
try it by hand.  Once you have got that fixed, archiving will continue normally.

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