Обсуждение: Testing hot standby on same server sharing same tablespace directory
Hi
I am testing hot standby on same server (version 9.5.2) , everything works well. I have a doubt regarding sharing of tablespace directory by these two databases .
I have created a tablespace and moved few objects and I happen to see that same directory was being used by standby node aka slave node and to my surprise it still works without any issues.
Is this expected ? I was expecting some sort of warnings from Postgres or am I missing something
Master $PGDATA
[pg952@pdb-poc01 pg_tblspc]$ pwd
/opt/v9.5.2/data/pg_tblspc
[pg952@pdb-poc01 pg_tblspc]$ ls -ltr
total 0
lrwxrwxrwx. 1 pg952 dba 17 Apr 27 16:10 16495 -> /opt/v9.5.2/tbsp1
[pg952@pdb-poc01 pg_tblspc]$
I can access the tables
[pg952@pdb-poc01 pg_tblspc]$ psql -d abcd -p 5432
psql (9.5.2)
Type "help" for help.
ssint=# select count(*) from t_random;
count
---------
8000000
(1 row)
ssint=#
Standby/Slave $PGDATA
[pg952@pdb-poc01 pg_tblspc]$ pwd
/home/postgres95/data/pg_tblspc
[pg952@pdb-poc01 pg_tblspc]$ ls -ltr
total 0
lrwxrwxrwx. 1 pg952 dba 17 Apr 27 16:10 16495 -> /opt/v9.5.2/tbsp1
[pg952@pdb-poc01 pg_tblspc]$
[pg952@pdb-poc01 16412]$ psql -d abcd -p 54321
psql (9.5.2)
Type "help" for help.
ssint=# delete from t_random;
ERROR: cannot execute DELETE in a read-only transaction
ssint=# select count(*) from t_random;
count
---------
8000000
(1 row)
ssint=#
-Sreekanth
On 04/28/2016 08:45 AM, Sreekanth Palluru wrote: > > Hi > > I am testing hot standby on same server (version 9.5.2) , everything works well. > I have a doubt regarding sharing of tablespace directory by these two databases . > > I have created a tablespace and moved few objects and I happen to see that same > directory was being used by standby node aka slave node and to my surprise it still works > without any issues. > > Is this expected ? Both instances will start and initially appear to function, but you are guaranteed to run into unexpected behaviour and some kind of corruption. Try e.g. creating and populating a table, then pausing replay on the standby; then drop and recreate/repopulate the same table. > I was expecting some sort of warnings from Postgres or am I missing something No, there's nothing which could warn about this particular scenario. Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Thanks Ian -----Original Message----- From: Ian Barwick [mailto:ian@2ndquadrant.com] Sent: Thursday, 28 April 2016 11:55 AM To: Sreekanth Palluru <Sreekanth.Palluru@superchoice.com.au>; pgsql-admin@postgresql.org Subject: Re: [ADMIN] Testing hot standby on same server sharing same tablespace directory On 04/28/2016 08:45 AM, Sreekanth Palluru wrote: > > Hi > > I am testing hot standby on same server (version 9.5.2) , everything works well. > I have a doubt regarding sharing of tablespace directory by these two databases . > > I have created a tablespace and moved few objects and I happen to see > that same directory was being used by standby node aka slave node and > to my surprise it still works without any issues. > > Is this expected ? Both instances will start and initially appear to function, but you are guaranteed to run into unexpected behaviour and somekind of corruption. Try e.g. creating and populating a table, then pausing replay on the standby; then drop and recreate/repopulate the sametable. > I was expecting some sort of warnings from Postgres or am I missing > something No, there's nothing which could warn about this particular scenario. Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services