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 <adrian.klaver@aklaver.com>
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.klaver@aklaver.com