Re: terminating walsender process due to replication timeout

[AYahorau]

Hello,
Thank You for the response.

Yes that's possible to monitor replication delay. But my questions were 
not about monitoring network issues. 

I use exactly wal_sender_timeout=1s because it allows to detect 
replication problems quickly.
So, I need clarification to the following  questions:
Is  it possible to use exactly this configuration and be sure that it will 
be work properly.
What did I do wrong? Should I correct my configuration somehow?
Is this the same issue  as mentioned here: 
https://www.postgresql.org/message-id/e082a56a-fd95-a250-3bae-0fff93832...@2ndquadrant.com
 
? If it is so, why I do I face this problem again?

Thank you in advance.
Best regards,
Andrei




From:   Rene Romero Benavides 
To: ayaho...@ibagroup.eu, 
Cc: Postgres General 
Date:   14/05/2019 20:12
Subject:Re: terminating walsender process due to replication 
timeout



To detect network issues maybe you could monitor replication delay.

On Mon, May 13, 2019 at 6:42 AM  wrote:
Hello PostgreSQL Community! 

I faced an issue on my linux machine using Postgres 11.3 . 
I have 2 nodes in db cluster: master and standby. 
I tried to perform a plenty of long-running  queries which lead to the 
databases desynchronization: 
terminating walsender process due to replication timeout 

Here is the output in debug mode: 
2019-05-13 13:21:33 FET 0 DEBUG:  sending replication keepalive 
2019-05-13 13:21:34 FET 0 DEBUG:  StartTransaction(1) name: unnamed; 
blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0 
2019-05-13 13:21:34 FET 0 DEBUG:  CommitTransaction(1) name: unnamed; 
blockState: END; state: INPROGRESS, xid/subid/cid: 0/1/0 
2019-05-13 13:21:34 FET 0 DEBUG:  StartTransaction(1) name: unnamed; 
blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0 
2019-05-13 13:21:34 FET 0 DEBUG:  CommitTransaction(1) name: unnamed; 
blockState: END; state: INPROGRESS, xid/subid/cid: 0/1/0 
2019-05-13 13:21:34 FET 0 DEBUG:  StartTransaction(1) name: unnamed; 
blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0 
2019-05-13 13:21:34 FET 0 DEBUG:  CommitTransaction(1) name: unnamed; 
blockState: END; state: INPROGRESS, xid/subid/cid: 0/1/0 
2019-05-13 13:21:34 FET 0 DEBUG:  StartTransaction(1) name: unnamed; 
blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0 
2019-05-13 13:21:34 FET 0 DEBUG:  CommitTransaction(1) name: unnamed; 
blockState: END; state: INPROGRESS, xid/subid/cid: 0/1/0 
2019-05-13 13:21:34 FET 0 DEBUG:  StartTransaction(1) name: unnamed; 
blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0 
2019-05-13 13:21:34 FET 0 DEBUG:  CommitTransaction(1) name: unnamed; 
blockState: END; state: INPROGRESS, xid/subid/cid: 0/1/0 
2019-05-13 13:21:34 FET 0 DEBUG:  StartTransaction(1) name: unnamed; 
blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0 
2019-05-13 13:21:34 FET 0 DEBUG:  CommitTransaction(1) name: unnamed; 
blockState: END; state: INPROGRESS, xid/subid/cid: 0/1/0 
2019-05-13 13:21:34 FET 0 LOG:  terminating walsender process due to 
replication timeout 


The issue is reproducible. I configure 2 nodes cluster, download 
demo_small.zip from https://edu.postgrespro.ru/ and run the following 
command: 
psql -U user1 -f demo_small.sql db1 
and I get the observed behaviour. 


I know that I can increase wal_sender_timeout value to avoid this 
behaviour (currently wal_sender_timeout is equal to 1 second.) 
To be honest I don't want to increase wal_sender_timeout because I would 
like to detect some network issues quickly. 

After having googled I found that someone faced a similar issue 
https://www.postgresql.org/message-id/e082a56a-fd95-a250-3bae-0fff93832...@2ndquadrant.com
 
which was fixed in  PostgreSQL 9.4.16. 


Is my issue the same as described here 
https://www.postgresql.org/message-id/e082a56a-fd95-a250-3bae-0fff93832...@2ndquadrant.com
 
? 
Is there any  other chance to avoid it without increasing 
wal_sender_timeout? 


Thank you in advance. 
Regards, 
Andrei


-- 
El genio es 1% inspiración y 99% transpiración.
Thomas Alva Edison
http://pglearn.blogspot.mx/

Re: perl path issue

[Prakash Ramakrishnan]

Hi Adrian,

I don't much about pgbackrest i think it will take backup from archives Now
only one the issue means perl cant load.

Regards,
Prakash.R

On Tue, May 14, 2019 at 8:15 PM Adrian Klaver 
wrote:

> On 5/14/19 7:40 AM, Prakash Ramakrishnan wrote:
> > Hi Adrian,
> >
> > Prod server retention periods ,
> >
> > [global]
> > repo1-path=/pgBACKUP/A4_sydcosafpp001
> > *retention-diff=3*
> > *retention-full=1*
> > *retention-archive=2*
> > start-fast=y
> > process-max=12
> > archive-async=y
> >
> > Dev server currently we dont have only restore need to do prod to dev.
>
> The issue is we are having trouble figuring out how the backups are
> getting from one location to another and in what order. So could you
> layout that out like I showed earlier:
>
> prod Pg db --pgBackRest--> some_host/some_dir/some_file  @daily
>
> dev Pg db <--pgBackRest -- some_host/some_dir/some_file  @3 weeks
>
> >
> > Regards,
> > Prakash.R
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>


-- 



Thanks,
Prakash.R
PostgreSQL - Offshore DBA support TCS / Nielsen Infrastructure Team On call
: +91-8939599426

Re: perl path issue

[Adrian Klaver]

On 5/15/19 2:37 AM, Prakash Ramakrishnan wrote:

Hi Adrian,

I don't much about pgbackrest i think it will take backup from archives 
Now only one the issue means perl cant load.


I don't use pgBackRest so I am speculating now, but I believe your issue 
is due to mixing local and remote operations. From a previous post:


[global]
repo1-host=sydcosafpp001.enterprisenet.org
repo1-host-user=postgres
repo1-host-config=/etc/pgbackrest.conf
repo1-path=/pgBACKUP/A4_sydcosafpp001/backup/A4_sydcosafpp001

[A4_sydcosafpp001]
pg1-path=/Postgres/prakash_pgbackrest

postgres(at)sydcosausd001(dot)enterprisenet(dot)org:/home/postgres

restore command - pgbackrest --stanza=A4_sydcosafpp001
--log-level-console=info --db-path=/Postgres/prakash_pgbackrest --delta
--process-max=2 --db-include=A4_Copy restore
--repo1-path=/pgBACKUP/A4_sydcosafpp001/backup/A4_sydcosafpp001

Looks to me are trying to use both a local(repo1-path) and 
remote(repo1-host*) repo at the same time. Given that the pgBackRest/EDB 
Postgres combination works on the prod server I would try doing a local 
only backup on the dev machine of the dev Postgres instance using the 
dev pgBackRest. This will help confirm whether there is a Perl issue on 
the dev machine or not.




Regards,
Prakash.R

On Tue, May 14, 2019 at 8:15 PM Adrian Klaver > wrote:


On 5/14/19 7:40 AM, Prakash Ramakrishnan wrote:
 > Hi Adrian,
 >
 > Prod server retention periods ,
 >
 > [global]
 > repo1-path=/pgBACKUP/A4_sydcosafpp001
 > *retention-diff=3*
 > *retention-full=1*
 > *retention-archive=2*
 > start-fast=y
 > process-max=12
 > archive-async=y
 >
 > Dev server currently we dont have only restore need to do prod to
dev.

The issue is we are having trouble figuring out how the backups are
getting from one location to another and in what order. So could you
layout that out like I showed earlier:

prod Pg db --pgBackRest--> some_host/some_dir/some_file  @daily

dev Pg db <--pgBackRest -- some_host/some_dir/some_file  @3 weeks

 >
 > Regards,
 > Prakash.R
 >


-- 
Adrian Klaver

adrian.kla...@aklaver.com 



--



Thanks,
Prakash.R
PostgreSQL - Offshore DBA support TCS / Nielsen Infrastructure Team On 
call : +91-8939599426



--
Adrian Klaver
adrian.kla...@aklaver.com

default_tablespace in 8.3 postgresql

[Julie Nishimura]

Hello, I have created new tablespace on a new drive yesterday and modified some 
of existing databases to use this tablespace as default, like this:
ALTER DATABASE xxx
SET default_tablespace = 'vol4';

So, this command did not physically move any existing tables/indices on the 
database xxx, but all new objects are currently being created on this 
tablespace (this was expected).

Please note, I also altered "template1" database to use new volume, to ensure 
all new databases will be created there by default.

ALTER DATABASE template1
SET default_tablespace = 'vol4';

However, I have not yet modified postgresql.conf, and our settings for 
default_tablespace and temp_tablespaces still set to use "vol3", because I was 
planning to leave some of databases out of vol4 (newer tablespace), since I did 
NOT altered their default_tablespace, and it was previously set to vol3.

Today, checking which tables were created where, I noticed, that all new 
tables, including tables from those databases which I did not alter to use new 
volume as default_tablespace, have been created on new volume 4. If I select 
from pg_database, I can see all my user databases have default_tablespace as 
vol4 (newer tablespace), even though I did not modify it. So, my question is, 
could altering "template1" database change default tablespace for all existing 
databases on a server???

PS. I could not make a mistake altering all, since I scripted my commands 
before, eliminating some of databases, and I do not see it in my script. Any 
thoughts?

Thank you

Re: default_tablespace in 8.3 postgresql

[Adrian Klaver]

On 5/15/19 12:39 PM, Julie Nishimura wrote:
Hello, I have created new tablespace on a new drive yesterday and 
modified some of existing databases to use this tablespace as default, 
like this:

ALTER DATABASE xxx
SET default_tablespace = 'vol4';

So, this command did not physically move any existing tables/indices on 
the database xxx, but all new objects are currently being created on 
this tablespace (this was expected).


Please note, I also altered "template1" database to use new volume, to 
ensure all new databases will be created there by default.


ALTER DATABASE template1
SET default_tablespace = 'vol4';

However, I have not yet modified postgresql.conf, and our settings for 
default_tablespace and temp_tablespaces still set to use "vol3", because 
I was planning to leave some of databases out of vol4 (newer 
tablespace), since I did NOT altered their default_tablespace, and it 
was previously set to vol3.


Today, checking which tables were created where, I noticed, that all new 
tables, including tables from those databases which I did not alter to 
use new volume as default_tablespace, have been created on new volume 4. 
If I select from pg_database, I can see all my user databases have 
default_tablespace as vol4 (newer tablespace), even though I did not 
modify it. So, my question is, could altering "template1" database 
change default tablespace for all existing databases on a server???


https://www.postgresql.org/docs/8.3/manage-ag-tablespaces.html

"If a database is created without specifying a tablespace for it, it 
uses the same tablespace as the template database it is copied from."




PS. I could not make a mistake altering all, since I scripted my 
commands before, eliminating some of databases, and I do not see it in 
my script. Any thoughts?


Thank you




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: default_tablespace in 8.3 postgresql

[Julie Nishimura]

Adrian, thanks for your reply.
This is very interesting behavior...
Those databases have been created long time ago, only some tables in them were 
created today.
So, make it more clear: lets say we have 5 user databases, 5 tables in each db, 
+ template0, template1, . I have modified 3 user databases and template1 to use 
new tablespace. However, since I have modified template1 (and most likely, 
those databases were copied from it in the past), it changed default_parameter 
for them as well. Interesting...

Thanks




From: Adrian Klaver 
Sent: Wednesday, May 15, 2019 12:47 PM
To: Julie Nishimura; pgsql-general@lists.postgresql.org; pgsql-general
Subject: Re: default_tablespace in 8.3 postgresql

On 5/15/19 12:39 PM, Julie Nishimura wrote:
> Hello, I have created new tablespace on a new drive yesterday and
> modified some of existing databases to use this tablespace as default,
> like this:
> ALTER DATABASE xxx
> SET default_tablespace = 'vol4';
>
> So, this command did not physically move any existing tables/indices on
> the database xxx, but all new objects are currently being created on
> this tablespace (this was expected).
>
> Please note, I also altered "template1" database to use new volume, to
> ensure all new databases will be created there by default.
>
> ALTER DATABASE template1
> SET default_tablespace = 'vol4';
>
> However, I have not yet modified postgresql.conf, and our settings for
> default_tablespace and temp_tablespaces still set to use "vol3", because
> I was planning to leave some of databases out of vol4 (newer
> tablespace), since I did NOT altered their default_tablespace, and it
> was previously set to vol3.
>
> Today, checking which tables were created where, I noticed, that all new
> tables, including tables from those databases which I did not alter to
> use new volume as default_tablespace, have been created on new volume 4.
> If I select from pg_database, I can see all my user databases have
> default_tablespace as vol4 (newer tablespace), even though I did not
> modify it. So, my question is, could altering "template1" database
> change default tablespace for all existing databases on a server???

https://www.postgresql.org/docs/8.3/manage-ag-tablespaces.html

"If a database is created without specifying a tablespace for it, it
uses the same tablespace as the template database it is copied from."

>
> PS. I could not make a mistake altering all, since I scripted my
> commands before, eliminating some of databases, and I do not see it in
> my script. Any thoughts?
>
> Thank you
>


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: default_tablespace in 8.3 postgresql

[David G. Johnston]

On Wed, May 15, 2019 at 1:01 PM Julie Nishimura 
wrote:

> However, since I have modified template1 (and most likely, those databases
> were copied from it in the past), it changed default_parameter for them as
> well.
>

This seems unlikely to be the case - changing template1 should not be
affecting other existing databases.

David J.

Re: default_tablespace in 8.3 postgresql

[Adrian Klaver]

On 5/15/19 1:01 PM, Julie Nishimura wrote:

Adrian, thanks for your reply.
This is very interesting behavior...
Those databases have been created long time ago, only some tables in 
them were created today.
So, make it more clear: lets say we have 5 user databases, 5 tables in 
each db, + template0, template1, . I have modified 3 user databases and 
template1 to use new tablespace. However, since I have modified 
template1 (and most likely, those databases were copied from it in the 
past), it changed default_parameter for them as well. Interesting...


I don't have an instance of 8.3 around so I can't test the above. On my 
11 instance I could not get the template1 to change tablespaces.


Can you confirm that template1 actually is set to 'vol4'?

I am with David I am not sure that even if it was changed that it would 
affect objects created in the past.


Using tablepaces is one of those actions where explicit is better then 
implicit. Whenever possible use the TABLESPACE clause to the object 
CREATE/ALTER command to be certain of where the object is going.





Thanks




*From:* Adrian Klaver 
*Sent:* Wednesday, May 15, 2019 12:47 PM
*To:* Julie Nishimura; pgsql-general@lists.postgresql.org; pgsql-general
*Subject:* Re: default_tablespace in 8.3 postgresql
On 5/15/19 12:39 PM, Julie Nishimura wrote:
Hello, I have created new tablespace on a new drive yesterday and 
modified some of existing databases to use this tablespace as default, 
like this:

ALTER DATABASE xxx
SET default_tablespace = 'vol4';

So, this command did not physically move any existing tables/indices on 
the database xxx, but all new objects are currently being created on 
this tablespace (this was expected).


Please note, I also altered "template1" database to use new volume, to 
ensure all new databases will be created there by default.


ALTER DATABASE template1
SET default_tablespace = 'vol4';

However, I have not yet modified postgresql.conf, and our settings for 
default_tablespace and temp_tablespaces still set to use "vol3", because 
I was planning to leave some of databases out of vol4 (newer 
tablespace), since I did NOT altered their default_tablespace, and it 
was previously set to vol3.


Today, checking which tables were created where, I noticed, that all new 
tables, including tables from those databases which I did not alter to 
use new volume as default_tablespace, have been created on new volume 4. 
If I select from pg_database, I can see all my user databases have 
default_tablespace as vol4 (newer tablespace), even though I did not 
modify it. So, my question is, could altering "template1" database 
change default tablespace for all existing databases on a server???


https://www.postgresql.org/docs/8.3/manage-ag-tablespaces.html

"If a database is created without specifying a tablespace for it, it
uses the same tablespace as the template database it is copied from."



PS. I could not make a mistake altering all, since I scripted my 
commands before, eliminating some of databases, and I do not see it in 
my script. Any thoughts?


Thank you




--
Adrian Klaver
adrian.kla...@aklaver.com



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: default_tablespace in 8.3 postgresql

[Julie Nishimura]

This puzzles me too! I found that bizarre myself. What is even more 
interesting, we have about 80 databases, and all of them now have 
default_tablespace=vol4, except only one - "control" database. The only 
explanation I would have that all of those databases which have tablespace vol4 
as default were copied from template1 without explicitly specified tablespace 
name...

postgres=# select * from pg_database limit 10;
   datname   | datdba | encoding | datistemplate | datallowconn 
| datconnlimit | datlastsysoid | datfrozenxid | dattablespace | 
datconfig |   datacl
-++--+---+--+--+---+--+---+---+-
 template0   | 10 |0 | t | f
|   -1 | 11510 |  378 |  1663 | 
  | {=c/postgres,postgres=CTc/postgres}
 postgres| 10 |0 | f | t
|   -1 | 11510 |  7554523 |  1663 | 
  |
 control |  16389 |6 | f | t
|   -1 | 11510 |  7554887 | 16384 | 
{default_tablespace=vol1} | {=T/dba,dba=CTc/dba,oper=c/dba,nagios=c/dba}
 template1   | 10 |0 | t | t
|   -1 | 11510 |  7554847 | 16384 | 
{default_tablespace=vol4} | {=c/postgres,postgres=CTc/postgres}
 conversion_alerts_fs|  16393 |0 | f | t
|   -1 | 11510 |  7554847 | 16384 | 
{default_tablespace=vol4} | {=T/build,build=CTc/build,tableau_readonly=c/build}
 conversion_feasibility_too  |  16393 |0 | f | t
|   -1 | 11510 |  7554847 | 16384 | 
{default_tablespace=vol4} |
 conversion_feasibility_tool |  16393 |0 | f | t
|   -1 | 11510 |  7554847 | 16384 | 
{default_tablespace=vol4} |
 custom_searches_au  |  16393 |6 | f | t
|   -1 | 11510 |  7554847 | 16384 | 
{default_tablespace=vol4} | {=T/build,build=CTc/build,tableau_readonly=c/build}

I've found that very bizarre myself, that default_tablespace would be changed 
for already existing databases without me altering it explicitly...
Sigh

From: David G. Johnston 
Sent: Wednesday, May 15, 2019 1:23 PM
To: Julie Nishimura
Cc: Adrian Klaver; pgsql-general@lists.postgresql.org
Subject: Re: default_tablespace in 8.3 postgresql

On Wed, May 15, 2019 at 1:01 PM Julie Nishimura 
mailto:juliez...@hotmail.com>> wrote:
However, since I have modified template1 (and most likely, those databases were 
copied from it in the past), it changed default_parameter for them as well.

This seems unlikely to be the case - changing template1 should not be affecting 
other existing databases.

David J.

Re: default_tablespace in 8.3 postgresql

[Rob Sargent]

On 5/15/19 2:57 PM, Julie Nishimura wrote:
This puzzles me too! I found that bizarre myself. What is even more 
interesting, we have about 80 databases, and all of them now have 
default_tablespace=vol4, except only one - "control" database. The 
only explanation I would have that all of those databases which have 
tablespace vol4 as default were copied from template1 without 
explicitly specified tablespace name...


postgres=# select * from pg_database limit 10;
           datname           | datdba | encoding | datistemplate | 
datallowconn | datconnlimit | datlastsysoid | datfrozenxid | 
dattablespace |         datconfig         |                   datacl

-++--+---+--+--+---+--+---+---+-
 template0                   |     10 |        0 | t       | f         
   |           -1 |         11510 |  378 |          1663 |             
              | {=c/postgres,postgres=CTc/postgres}
 postgres                    |     10 |        0 | f       | t         
   |           -1 |         11510 |  7554523 |          1663 |         
                  |
 control                     |  16389 |        6 | f       | t         
   |           -1 |         11510 |  7554887 |         16384 | 
{default_tablespace=vol1} | {=T/dba,dba=CTc/dba,oper=c/dba,nagios=c/dba}
 template1                   |     10 |        0 | t       | t         
   |           -1 |         11510 |  7554847 |         16384 | 
{default_tablespace=vol4} | {=c/postgres,postgres=CTc/postgres}
 conversion_alerts_fs        |  16393 |        0 | f       | t         
   |           -1 |         11510 |  7554847 |         16384 | 
{default_tablespace=vol4} | 
{=T/build,build=CTc/build,tableau_readonly=c/build}
 conversion_feasibility_too  |  16393 |        0 | f       | t         
   |           -1 |         11510 |  7554847 |         16384 | 
{default_tablespace=vol4} |
 conversion_feasibility_tool |  16393 |        0 | f       | t         
   |           -1 |         11510 |  7554847 |         16384 | 
{default_tablespace=vol4} |
 custom_searches_au          |  16393 |        6 | f       | t         
   |           -1 |         11510 |  7554847 |         16384 | 
{default_tablespace=vol4} | 
{=T/build,build=CTc/build,tableau_readonly=c/build}


I've found that very bizarre myself, that default_tablespace would be 
changed for already existing databases without me altering it 
explicitly...

Sigh

*From:* David G. Johnston 
*Sent:* Wednesday, May 15, 2019 1:23 PM
*To:* Julie Nishimura
*Cc:* Adrian Klaver; pgsql-general@lists.postgresql.org
*Subject:* Re: default_tablespace in 8.3 postgresql
On Wed, May 15, 2019 at 1:01 PM Julie Nishimura > wrote:


However, since I have modified template1 (and most likely, those
databases were copied from it in the past), it changed
default_parameter for them as well.


This seems unlikely to be the case - changing template1 should not be 
affecting other existing databases.


David J.



Previous actions may have altered (accidentally) the default table space 
for those databases but this doesn't show that the tables of old are now 
on vol4 does it?.  Would have taken some time to move 80 non-trivial 
databases.

Re: default_tablespace in 8.3 postgresql

[Adrian Klaver]

On 5/15/19 1:57 PM, Julie Nishimura wrote:
This puzzles me too! I found that bizarre myself. What is even more 
interesting, we have about 80 databases, and all of them now have 
default_tablespace=vol4, except only one - "control" database. The only 
explanation I would have that all of those databases which have 
tablespace vol4 as default were copied from template1 without explicitly 
specified tablespace name...


postgres=# select * from pg_database limit 10;
            datname           | datdba | encoding | datistemplate | 
datallowconn | datconnlimit | datlastsysoid | datfrozenxid | 
dattablespace |         datconfig         |                       datacl

-++--+---+--+--+---+--+---+---+-
  template0                   |     10 |        0 | t             | f   
          |           -1 |         11510 |          378 |          1663 
|                           | {=c/postgres,postgres=CTc/postgres}
  postgres                    |     10 |        0 | f             | t   
          |           -1 |         11510 |      7554523 |          1663 
|                           |
  control                     |  16389 |        6 | f             | t   
          |           -1 |         11510 |      7554887 |         16384 
| {default_tablespace=vol1} | {=T/dba,dba=CTc/dba,oper=c/dba,nagios=c/dba}
  template1                   |     10 |        0 | t             | t   
          |           -1 |         11510 |      7554847 |         16384 
| {default_tablespace=vol4} | {=c/postgres,postgres=CTc/postgres}
  conversion_alerts_fs        |  16393 |        0 | f             | t   
          |           -1 |         11510 |      7554847 |         16384 
| {default_tablespace=vol4} | 
{=T/build,build=CTc/build,tableau_readonly=c/build}
  conversion_feasibility_too  |  16393 |        0 | f             | t   
          |           -1 |         11510 |      7554847 |         16384 
| {default_tablespace=vol4} |
  conversion_feasibility_tool |  16393 |        0 | f             | t   
          |           -1 |         11510 |      7554847 |         16384 
| {default_tablespace=vol4} |
  custom_searches_au          |  16393 |        6 | f             | t   
          |           -1 |         11510 |      7554847 |         16384 
| {default_tablespace=vol4} | 
{=T/build,build=CTc/build,tableau_readonly=c/build}


I've found that very bizarre myself, that default_tablespace would be 
changed for already existing databases without me altering it explicitly...

Sigh


Can we see an example of the script you used to modify the databases?



*From:* David G. Johnston 
*Sent:* Wednesday, May 15, 2019 1:23 PM
*To:* Julie Nishimura
*Cc:* Adrian Klaver; pgsql-general@lists.postgresql.org
*Subject:* Re: default_tablespace in 8.3 postgresql
On Wed, May 15, 2019 at 1:01 PM Julie Nishimura > wrote:


However, since I have modified template1 (and most likely, those
databases were copied from it in the past), it changed
default_parameter for them as well.


This seems unlikely to be the case - changing template1 should not be 
affecting other existing databases.


David J.




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: default_tablespace in 8.3 postgresql

[Julie Nishimura]

Sure, thank you guys!

CREATE TABLESPACE vol4
 OWNER postgres
 LOCATION '/data/vol4';

ALTER DATABASE tables_ericb
SET default_tablespace = 'vol4';
 ALTER DATABASE conversion_feasibility_too
 SET default_tablespace = 'vol4';

... and so on for 60 dbs...

but not for all 70+


From: Adrian Klaver 
Sent: Wednesday, May 15, 2019 2:11 PM
To: Julie Nishimura; David G. Johnston
Cc: pgsql-general@lists.postgresql.org
Subject: Re: default_tablespace in 8.3 postgresql

On 5/15/19 1:57 PM, Julie Nishimura wrote:
> This puzzles me too! I found that bizarre myself. What is even more
> interesting, we have about 80 databases, and all of them now have
> default_tablespace=vol4, except only one - "control" database. The only
> explanation I would have that all of those databases which have
> tablespace vol4 as default were copied from template1 without explicitly
> specified tablespace name...
>
> postgres=# select * from pg_database limit 10;
> datname   | datdba | encoding | datistemplate |
> datallowconn | datconnlimit | datlastsysoid | datfrozenxid |
> dattablespace | datconfig |   datacl
> -++--+---+--+--+---+--+---+---+-
>   template0   | 10 |0 | t | f
>   |   -1 | 11510 |  378 |  1663
> |   | {=c/postgres,postgres=CTc/postgres}
>   postgres| 10 |0 | f | t
>   |   -1 | 11510 |  7554523 |  1663
> |   |
>   control |  16389 |6 | f | t
>   |   -1 | 11510 |  7554887 | 16384
> | {default_tablespace=vol1} | {=T/dba,dba=CTc/dba,oper=c/dba,nagios=c/dba}
>   template1   | 10 |0 | t | t
>   |   -1 | 11510 |  7554847 | 16384
> | {default_tablespace=vol4} | {=c/postgres,postgres=CTc/postgres}
>   conversion_alerts_fs|  16393 |0 | f | t
>   |   -1 | 11510 |  7554847 | 16384
> | {default_tablespace=vol4} |
> {=T/build,build=CTc/build,tableau_readonly=c/build}
>   conversion_feasibility_too  |  16393 |0 | f | t
>   |   -1 | 11510 |  7554847 | 16384
> | {default_tablespace=vol4} |
>   conversion_feasibility_tool |  16393 |0 | f | t
>   |   -1 | 11510 |  7554847 | 16384
> | {default_tablespace=vol4} |
>   custom_searches_au  |  16393 |6 | f | t
>   |   -1 | 11510 |  7554847 | 16384
> | {default_tablespace=vol4} |
> {=T/build,build=CTc/build,tableau_readonly=c/build}
>
> I've found that very bizarre myself, that default_tablespace would be
> changed for already existing databases without me altering it explicitly...
> Sigh

Can we see an example of the script you used to modify the databases?

> 
> *From:* David G. Johnston 
> *Sent:* Wednesday, May 15, 2019 1:23 PM
> *To:* Julie Nishimura
> *Cc:* Adrian Klaver; pgsql-general@lists.postgresql.org
> *Subject:* Re: default_tablespace in 8.3 postgresql
> On Wed, May 15, 2019 at 1:01 PM Julie Nishimura  > wrote:
>
> However, since I have modified template1 (and most likely, those
> databases were copied from it in the past), it changed
> default_parameter for them as well.
>
>
> This seems unlikely to be the case - changing template1 should not be
> affecting other existing databases.
>
> David J.
>


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: default_tablespace in 8.3 postgresql

[Adrian Klaver]

On 5/15/19 2:15 PM, Julie Nishimura wrote:

Sure, thank you guys!

CREATE TABLESPACE vol4
  OWNER postgres
  LOCATION '/data/vol4';

ALTER DATABASE tables_ericb
SET default_tablespace = 'vol4';
  ALTER DATABASE conversion_feasibility_too
  SET default_tablespace = 'vol4';

... and so on for 60 dbs...

but not for all 70+


But it was not for all 70+. The control db still is still set at vol1.

Are there others not set to vol4?


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: default_tablespace in 8.3 postgresql

[Julie Nishimura]

Adrian, only first 3 (which are template0, postgres, control). Rest of them are 
set to vol4.

The only explanation I would have that all of those databases which have 
tablespace vol4 as default were copied from template1 without explicitly 
specified tablespace name, and "control" was created with the explicit 
tablespace in it?... I dont have any other good explanations...


From: Adrian Klaver 
Sent: Wednesday, May 15, 2019 2:18 PM
To: Julie Nishimura; David G. Johnston
Cc: pgsql-general@lists.postgresql.org
Subject: Re: default_tablespace in 8.3 postgresql

On 5/15/19 2:15 PM, Julie Nishimura wrote:
> Sure, thank you guys!
>
> CREATE TABLESPACE vol4
>   OWNER postgres
>   LOCATION '/data/vol4';
>
> ALTER DATABASE tables_ericb
> SET default_tablespace = 'vol4';
>   ALTER DATABASE conversion_feasibility_too
>   SET default_tablespace = 'vol4';
>
> ... and so on for 60 dbs...
>
> but not for all 70+

But it was not for all 70+. The control db still is still set at vol1.

Are there others not set to vol4?


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: default_tablespace in 8.3 postgresql

[Adrian Klaver]

On 5/15/19 2:45 PM, Julie Nishimura wrote:
Adrian, only first 3 (which are template0, postgres, control). Rest of 
them are set to vol4.


Do the script(s) reflect this?

In other words did you have a script for each and every database and was 
the default_tablespace set different in some of them?




The only explanation I would have that all of those databases which have 
tablespace vol4 as default were copied from template1 without explicitly 
specified tablespace name, and "control" was created with the explicit 
tablespace in it?... I dont have any other good explanations...



*From:* Adrian Klaver 
*Sent:* Wednesday, May 15, 2019 2:18 PM
*To:* Julie Nishimura; David G. Johnston
*Cc:* pgsql-general@lists.postgresql.org
*Subject:* Re: default_tablespace in 8.3 postgresql
On 5/15/19 2:15 PM, Julie Nishimura wrote:

Sure, thank you guys!

CREATE TABLESPACE vol4
   OWNER postgres
   LOCATION '/data/vol4';

ALTER DATABASE tables_ericb
SET default_tablespace = 'vol4';
   ALTER DATABASE conversion_feasibility_too
   SET default_tablespace = 'vol4';

... and so on for 60 dbs...

but not for all 70+


But it was not for all 70+. The control db still is still set at vol1.

Are there others not set to vol4?


--
Adrian Klaver
adrian.kla...@aklaver.com



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: default_tablespace in 8.3 postgresql

[Julie Nishimura]

No, I did not have them at all in my script. I did not touch neither these 
three dbs, nor like some others that I did not intend to alter, but they are 
altered regardless. Or unless this is what pg_database reports back to me... 
What is even more interesting, if I use PGAdmin for one of the dbs that I was 
not intended to alter and leave it on vol3, this is what it shows to me now. 
Please note, it shows both -  "Default tablespace" (as vol3), and 
"default_tablespace" (as vol4)... Hmm...
[cid:45dce903-fa47-4e59-b3af-f07fa991f55b]




From: Adrian Klaver 
Sent: Wednesday, May 15, 2019 2:48 PM
To: Julie Nishimura; David G. Johnston
Cc: pgsql-general@lists.postgresql.org
Subject: Re: default_tablespace in 8.3 postgresql

On 5/15/19 2:45 PM, Julie Nishimura wrote:
> Adrian, only first 3 (which are template0, postgres, control). Rest of
> them are set to vol4.

Do the script(s) reflect this?

In other words did you have a script for each and every database and was
the default_tablespace set different in some of them?

>
> The only explanation I would have that all of those databases which have
> tablespace vol4 as default were copied from template1 without explicitly
> specified tablespace name, and "control" was created with the explicit
> tablespace in it?... I dont have any other good explanations...
>
> 
> *From:* Adrian Klaver 
> *Sent:* Wednesday, May 15, 2019 2:18 PM
> *To:* Julie Nishimura; David G. Johnston
> *Cc:* pgsql-general@lists.postgresql.org
> *Subject:* Re: default_tablespace in 8.3 postgresql
> On 5/15/19 2:15 PM, Julie Nishimura wrote:
>> Sure, thank you guys!
>>
>> CREATE TABLESPACE vol4
>>   OWNER postgres
>>   LOCATION '/data/vol4';
>>
>> ALTER DATABASE tables_ericb
>> SET default_tablespace = 'vol4';
>>   ALTER DATABASE conversion_feasibility_too
>>   SET default_tablespace = 'vol4';
>>
>> ... and so on for 60 dbs...
>>
>> but not for all 70+
>
> But it was not for all 70+. The control db still is still set at vol1.
>
> Are there others not set to vol4?
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: default_tablespace in 8.3 postgresql

[Adrian Klaver]

On 5/15/19 3:01 PM, Julie Nishimura wrote:
No, I did not have them at all in my script. I did not touch neither 
these three dbs, nor like some others that I did not intend to alter, 
but they are altered regardless. Or unless this is what pg_database 
reports back to me... What is even more interesting, if I use PGAdmin 
for one of the dbs that I was not intended to alter and leave it on 
vol3, this is what it shows to me now. Please note, it shows both -  
"Default tablespace" (as vol3), and "default_tablespace" (as vol4)... Hmm...


One is from the postgresql.conf setting(vol3).
The other is from the database default_tablespace setting.







*From:* Adrian Klaver 
*Sent:* Wednesday, May 15, 2019 2:48 PM
*To:* Julie Nishimura; David G. Johnston
*Cc:* pgsql-general@lists.postgresql.org
*Subject:* Re: default_tablespace in 8.3 postgresql
On 5/15/19 2:45 PM, Julie Nishimura wrote:
Adrian, only first 3 (which are template0, postgres, control). Rest of 
them are set to vol4.


Do the script(s) reflect this?

In other words did you have a script for each and every database and was
the default_tablespace set different in some of them?



The only explanation I would have that all of those databases which have 
tablespace vol4 as default were copied from template1 without explicitly 
specified tablespace name, and "control" was created with the explicit 
tablespace in it?... I dont have any other good explanations...



*From:* Adrian Klaver 
*Sent:* Wednesday, May 15, 2019 2:18 PM
*To:* Julie Nishimura; David G. Johnston
*Cc:* pgsql-general@lists.postgresql.org
*Subject:* Re: default_tablespace in 8.3 postgresql
On 5/15/19 2:15 PM, Julie Nishimura wrote:

Sure, thank you guys!

CREATE TABLESPACE vol4
   OWNER postgres
   LOCATION '/data/vol4';

ALTER DATABASE tables_ericb
SET default_tablespace = 'vol4';
   ALTER DATABASE conversion_feasibility_too
   SET default_tablespace = 'vol4';

... and so on for 60 dbs...

but not for all 70+


But it was not for all 70+. The control db still is still set at vol1.

Are there others not set to vol4?


--
Adrian Klaver
adrian.kla...@aklaver.com



--
Adrian Klaver
adrian.kla...@aklaver.com



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: default_tablespace in 8.3 postgresql

[Julie Nishimura]

that makes sense, thanks!


From: Adrian Klaver 
Sent: Wednesday, May 15, 2019 3:15 PM
To: Julie Nishimura; David G. Johnston
Cc: pgsql-general@lists.postgresql.org
Subject: Re: default_tablespace in 8.3 postgresql

On 5/15/19 3:01 PM, Julie Nishimura wrote:
> No, I did not have them at all in my script. I did not touch neither
> these three dbs, nor like some others that I did not intend to alter,
> but they are altered regardless. Or unless this is what pg_database
> reports back to me... What is even more interesting, if I use PGAdmin
> for one of the dbs that I was not intended to alter and leave it on
> vol3, this is what it shows to me now. Please note, it shows both -
> "Default tablespace" (as vol3), and "default_tablespace" (as vol4)... Hmm...

One is from the postgresql.conf setting(vol3).
The other is from the database default_tablespace setting.

>
>
>
>
> 
> *From:* Adrian Klaver 
> *Sent:* Wednesday, May 15, 2019 2:48 PM
> *To:* Julie Nishimura; David G. Johnston
> *Cc:* pgsql-general@lists.postgresql.org
> *Subject:* Re: default_tablespace in 8.3 postgresql
> On 5/15/19 2:45 PM, Julie Nishimura wrote:
>> Adrian, only first 3 (which are template0, postgres, control). Rest of
>> them are set to vol4.
>
> Do the script(s) reflect this?
>
> In other words did you have a script for each and every database and was
> the default_tablespace set different in some of them?
>
>>
>> The only explanation I would have that all of those databases which have
>> tablespace vol4 as default were copied from template1 without explicitly
>> specified tablespace name, and "control" was created with the explicit
>> tablespace in it?... I dont have any other good explanations...
>>
>> 
>> *From:* Adrian Klaver 
>> *Sent:* Wednesday, May 15, 2019 2:18 PM
>> *To:* Julie Nishimura; David G. Johnston
>> *Cc:* pgsql-general@lists.postgresql.org
>> *Subject:* Re: default_tablespace in 8.3 postgresql
>> On 5/15/19 2:15 PM, Julie Nishimura wrote:
>>> Sure, thank you guys!
>>>
>>> CREATE TABLESPACE vol4
>>>   OWNER postgres
>>>   LOCATION '/data/vol4';
>>>
>>> ALTER DATABASE tables_ericb
>>> SET default_tablespace = 'vol4';
>>>   ALTER DATABASE conversion_feasibility_too
>>>   SET default_tablespace = 'vol4';
>>>
>>> ... and so on for 60 dbs...
>>>
>>> but not for all 70+
>>
>> But it was not for all 70+. The control db still is still set at vol1.
>>
>> Are there others not set to vol4?
>>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: default_tablespace in 8.3 postgresql

[Adrian Klaver]

On 5/15/19 3:01 PM, Julie Nishimura wrote:
No, I did not have them at all in my script. I did not touch neither 
these three dbs, nor like some others that I did not intend to alter, 
but they are altered regardless. Or unless this is what pg_database 
reports back to me... What is even more interesting, if I use PGAdmin 
for one of the dbs that I was not intended to alter and leave it on 
vol3, this is what it shows to me now. Please note, it shows both -  
"Default tablespace" (as vol3), and "default_tablespace" (as vol4)... Hmm...





You didn't happen to do a:

ALTER ROLE some_role SET default_tablespace = 'vol4';


Do:

select rolname, rolconfig from pg_roles;





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: default_tablespace in 8.3 postgresql

[Julie Nishimura]

No, Rob, old existing tables are still on other volumes (the are almost 20 tb), 
only new tables are now created on new volume


From: Rob Sargent 
Sent: Wednesday, May 15, 2019 2:02 PM
To: pgsql-general@lists.postgresql.org
Subject: Re: default_tablespace in 8.3 postgresql



On 5/15/19 2:57 PM, Julie Nishimura wrote:
This puzzles me too! I found that bizarre myself. What is even more 
interesting, we have about 80 databases, and all of them now have 
default_tablespace=vol4, except only one - "control" database. The only 
explanation I would have that all of those databases which have tablespace vol4 
as default were copied from template1 without explicitly specified tablespace 
name...

postgres=# select * from pg_database limit 10;
   datname   | datdba | encoding | datistemplate | datallowconn 
| datconnlimit | datlastsysoid | datfrozenxid | dattablespace | 
datconfig |   datacl
-++--+---+--+--+---+--+---+---+-
 template0   | 10 |0 | t | f
|   -1 | 11510 |  378 |  1663 | 
  | {=c/postgres,postgres=CTc/postgres}
 postgres| 10 |0 | f | t
|   -1 | 11510 |  7554523 |  1663 | 
  |
 control |  16389 |6 | f | t
|   -1 | 11510 |  7554887 | 16384 | 
{default_tablespace=vol1} | {=T/dba,dba=CTc/dba,oper=c/dba,nagios=c/dba}
 template1   | 10 |0 | t | t
|   -1 | 11510 |  7554847 | 16384 | 
{default_tablespace=vol4} | {=c/postgres,postgres=CTc/postgres}
 conversion_alerts_fs|  16393 |0 | f | t
|   -1 | 11510 |  7554847 | 16384 | 
{default_tablespace=vol4} | {=T/build,build=CTc/build,tableau_readonly=c/build}
 conversion_feasibility_too  |  16393 |0 | f | t
|   -1 | 11510 |  7554847 | 16384 | 
{default_tablespace=vol4} |
 conversion_feasibility_tool |  16393 |0 | f | t
|   -1 | 11510 |  7554847 | 16384 | 
{default_tablespace=vol4} |
 custom_searches_au  |  16393 |6 | f | t
|   -1 | 11510 |  7554847 | 16384 | 
{default_tablespace=vol4} | {=T/build,build=CTc/build,tableau_readonly=c/build}

I've found that very bizarre myself, that default_tablespace would be changed 
for already existing databases without me altering it explicitly...
Sigh

From: David G. Johnston 

Sent: Wednesday, May 15, 2019 1:23 PM
To: Julie Nishimura
Cc: Adrian Klaver; 
pgsql-general@lists.postgresql.org
Subject: Re: default_tablespace in 8.3 postgresql

On Wed, May 15, 2019 at 1:01 PM Julie Nishimura 
mailto:juliez...@hotmail.com>> wrote:
However, since I have modified template1 (and most likely, those databases were 
copied from it in the past), it changed default_parameter for them as well.

This seems unlikely to be the case - changing template1 should not be affecting 
other existing databases.

David J.



Previous actions may have altered (accidentally) the default table space for 
those databases but this doesn't show that the tables of old are now on vol4 
does it?.  Would have taken some time to move 80 non-trivial databases.

Re: default_tablespace in 8.3 postgresql

[Julie Nishimura]

Nope, I did not...

pg_roles only shows "rolconfig" as {default_transaction_read_only=true} for 
some users...

The only other commands I ran between creation of tablespace and alter dbs are:

grant all on tablespace vol4 to public;
grant create on tablespace vol4 to public;


From: Adrian Klaver 
Sent: Wednesday, May 15, 2019 3:22 PM
To: Julie Nishimura; David G. Johnston
Cc: pgsql-general@lists.postgresql.org
Subject: Re: default_tablespace in 8.3 postgresql

On 5/15/19 3:01 PM, Julie Nishimura wrote:
> No, I did not have them at all in my script. I did not touch neither
> these three dbs, nor like some others that I did not intend to alter,
> but they are altered regardless. Or unless this is what pg_database
> reports back to me... What is even more interesting, if I use PGAdmin
> for one of the dbs that I was not intended to alter and leave it on
> vol3, this is what it shows to me now. Please note, it shows both -
> "Default tablespace" (as vol3), and "default_tablespace" (as vol4)... Hmm...
>


You didn't happen to do a:

ALTER ROLE some_role SET default_tablespace = 'vol4';


Do:

select rolname, rolconfig from pg_roles;





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: default_tablespace in 8.3 postgresql

[Adrian Klaver]

On 5/15/19 3:28 PM, Julie Nishimura wrote:

Nope, I did not...

pg_roles only shows "rolconfig" as {default_transaction_read_only=true} 
for some users...


The only other commands I ran between creation of tablespace and alter 
dbs are:


grant all on tablespace vol4 to public;
grant create on tablespace vol4 to public;



The only thing I have left, assuming it will not interfere is:

BEGIN;

ALTER DATABASE template1
SET default_tablespace = default;


Check pg_database

ROLLBACK;

That may help confirm whether template1 is the culprit. More for 
completeness sake as 8.3 is 6 years past EOL, so there is not much that 
can be done about it.



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: default_tablespace in 8.3 postgresql

[Julie Nishimura]

Guys, thank you everybody who tried to solve this mystery! It is solved now. I 
went through the logs and found those "alter database" commands, based on host 
and time, I've just found shell script which was executing from crontab and 
setting all databases (except system ones and control) default_tablespace to 
the emptiest volume...

Thanks everybody...


From: Adrian Klaver 
Sent: Wednesday, May 15, 2019 3:35 PM
To: Julie Nishimura; David G. Johnston
Cc: pgsql-general@lists.postgresql.org
Subject: Re: default_tablespace in 8.3 postgresql

On 5/15/19 3:28 PM, Julie Nishimura wrote:
> Nope, I did not...
>
> pg_roles only shows "rolconfig" as {default_transaction_read_only=true}
> for some users...
>
> The only other commands I ran between creation of tablespace and alter
> dbs are:
>
> grant all on tablespace vol4 to public;
> grant create on tablespace vol4 to public;
>

The only thing I have left, assuming it will not interfere is:

BEGIN;

ALTER DATABASE template1
SET default_tablespace = default;


Check pg_database

ROLLBACK;

That may help confirm whether template1 is the culprit. More for
completeness sake as 8.3 is 6 years past EOL, so there is not much that
can be done about it.


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: default_tablespace in 8.3 postgresql

[Adrian Klaver]

On 5/15/19 5:29 PM, Julie Nishimura wrote:
Guys, thank you everybody who tried to solve this mystery! It is solved 
now. I went through the logs and found those "alter database" commands, 
based on host and time, I've just found shell script which was executing 
from crontab and setting all databases (except system ones and control) 
default_tablespace to the emptiest volume...


Aah, that explains it. Good to find the cause.


Thanks everybody...





--
Adrian Klaver
adrian.kla...@aklaver.com