Hello,
Thanks for your response. I agree with all your suggestions.
At this moment, I'm concerned about the WAL archiving.
I'm seeing below error on pg3 (DR server) in log. Is this expected ?
cp: cannot stat '/mnt/server/archivedir/00000001000000000000000D': No such file or directory
On master, I see below info. I would like to know why the WALs are not being archived.
postgres@pg1:~/9.6/main/pg_xlog$ ls -lrth
total 65M
-rw------- 1 postgres postgres 16M Jul 2 13:09 00000001000000000000000E
-rw------- 1 postgres postgres 16M Jul 2 13:13 00000001000000000000000F
-rw------- 1 postgres postgres 16M Jul 2 13:13 000000010000000000000010
-rw------- 1 postgres postgres 302 Jul 2 13:13 00000001000000000000000C.00000028.backup
drwx------ 2 postgres postgres 4.0K Jul 2 13:23 archive_status
-rw------- 1 postgres postgres 16M Jul 2 15:38 00000001000000000000000D
total 65M
-rw------- 1 postgres postgres 16M Jul 2 13:09 00000001000000000000000E
-rw------- 1 postgres postgres 16M Jul 2 13:13 00000001000000000000000F
-rw------- 1 postgres postgres 16M Jul 2 13:13 000000010000000000000010
-rw------- 1 postgres postgres 302 Jul 2 13:13 00000001000000000000000C.00000028.backup
drwx------ 2 postgres postgres 4.0K Jul 2 13:23 archive_status
-rw------- 1 postgres postgres 16M Jul 2 15:38 00000001000000000000000D
postgres@pg1:~/9.6/main/pg_xlog$ ls -lrt /mnt/server/archivedir/
total 32788
drwx------ 2 postgres postgres 16384 Jul 1 14:44 lost+found
-rw------- 1 postgres postgres 16777216 Jul 2 13:13 00000001000000000000000B
-rw------- 1 postgres postgres 16777216 Jul 2 13:13 00000001000000000000000C
-rw------- 1 postgres postgres 302 Jul 2 13:13 00000001000000000000000C.00000028.backup
total 32788
drwx------ 2 postgres postgres 16384 Jul 1 14:44 lost+found
-rw------- 1 postgres postgres 16777216 Jul 2 13:13 00000001000000000000000B
-rw------- 1 postgres postgres 16777216 Jul 2 13:13 00000001000000000000000C
-rw------- 1 postgres postgres 302 Jul 2 13:13 00000001000000000000000C.00000028.backup
On Thu, Jul 2, 2020 at 8:56 PM Stephen Frost <sfrost@xxxxxxxxxxx> wrote:
Greetings,
* Praveen Kumar K S (praveenssit@xxxxxxxxx) wrote:
> I'm using 9.6
That's quite old- I'd strongly recommend that you look at using a newer
version.
> I have 3 servers. Let us call it pg1,pg2,pg3
>
> pg1 is master
> pg2 is hot stand by
> pg3 is a DR server which doesn't serve any requests. hot_standby is not
> enabled in postgresql.conf on DR server.
If hot_standby isn't enable then you won't be able to connect- which is
entirely fine, but if that's what you want then I'm not sure what the
error is that you're concerned about..?
> pg1 and pg2 are in the same network and are in sync.
Ok. Note that having only one replica when you're using syncronous
replication will lead to a situation where, if either node fails, the
database system won't accept writes.
> I followed official documentation and used the cp command. Are there any
> other best approaches ?
The documentation provides an example of how to use the command, using
'cp' isn't actually recommended. I'd strongly suggest you look at
proper backup/archiving solutions for PostgreSQL like pgbackrest to use
instead.
Thanks,
Stephen
--
Regards,
K S Praveen Kumar
M: +91-9986855625
M: +91-9986855625
