Re: query performance after database rename

How you are identifying the slowdown in database system ? Are you executing
some queries through psql or pgadmin and checking execution time or from
application and find slowness ? Are you using connection pooling ? If so
which one it is application or postgresql tools (pgpool, pgbouncer etc).

Thanks
Srinivas

On Wed, Nov 21, 2018 at 5:30 PM Zwettler Markus (OIZ) <
Markus(dot)Zwettler(at)zuerich(dot)ch> wrote:

> Hi,
>
>
>
> We have to exchange two databases db1 ó db2.
>
>
>
> alter database [db1|db2] with allow_connections=false;
>
> select pg_terminate_backend (pg_stat_activity.pid) from pg_stat_activity
> where pg_stat_activity.datname in ('db1', 'db2') and pid <>
> pg_backend_pid();
>
> alter database [db1|db2|temp] rename to [temp|db1|db2];
>
> alter database [db1|db2] with allow_connections=true;
>
>
>
> Version 9.6
>
>
>
> Cheers, Markus
>
>
>
>
>
>
>
>
>
> *Von:* srinivas oguri <srinivasoguri7(at)gmail(dot)com>
> *Gesendet:* Mittwoch, 21. November 2018 12:24
> *An:* Zwettler Markus (OIZ) <Markus(dot)Zwettler(at)zuerich(dot)ch>
> *Cc:* pgsql-admin(at)lists(dot)postgresql(dot)org
> *Betreff:* Re: query performance after database rename
>
>
>
> Hi Markus,
>
>
>
> Can you give an example ? what is the postgres version your are using?
> what exactly you are doing ?
>
>
>
> PostgreSQL do not perform any flush operation after renaming. Below is the
> debug log of postgresql
>
>
>
> < 2018-11-21 11:12:19.481 UTC > DEBUG: postmaster child[14261]: starting
> with (
>
> < 2018-11-21 11:12:19.481 UTC > DEBUG: postgres
>
> < 2018-11-21 11:12:19.481 UTC > DEBUG: )
>
> < 2018-11-21 11:12:19.481 UTC > DEBUG: InitPostgres
>
> < 2018-11-21 11:12:19.482 UTC > DEBUG: my backend ID is 3
>
> < 2018-11-21 11:12:19.482 UTC > DEBUG: StartTransaction
>
> < 2018-11-21 11:12:19.482 UTC > DEBUG: name: unnamed; blockState:
> DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
>
> < 2018-11-21 11:12:19.483 UTC > DEBUG: CommitTransaction
>
> < 2018-11-21 11:12:19.483 UTC > DEBUG: name: unnamed; blockState:
> STARTED; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
>
> < 2018-11-21 11:12:19.483 UTC > DEBUG: StartTransactionCommand
>
> < 2018-11-21 11:12:19.483 UTC > DEBUG: StartTransaction
>
> < 2018-11-21 11:12:19.483 UTC > DEBUG: name: unnamed; blockState:
> DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
>
> < 2018-11-21 11:12:19.484 UTC > DEBUG: CommitTransactionCommand
>
> < 2018-11-21 11:12:19.484 UTC > DEBUG: CommitTransaction
>
> < 2018-11-21 11:12:19.484 UTC > DEBUG: name: unnamed; blockState:
> STARTED; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
>
> < 2018-11-21 11:12:19.484 UTC > LOG: duration: 0.367 ms statement:
> SELECT pg_backend_pid()
>
> < 2018-11-21 11:12:19.484 UTC > DEBUG: StartTransactionCommand
>
> < 2018-11-21 11:12:19.484 UTC > DEBUG: StartTransaction
>
> < 2018-11-21 11:12:19.484 UTC > DEBUG: name: unnamed; blockState:
> DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
>
> < 2018-11-21 11:12:19.484 UTC > DEBUG: ProcessUtility
>
> < 2018-11-21 11:12:19.484 UTC > DEBUG: CommitTransactionCommand
>
> < 2018-11-21 11:12:19.484 UTC > DEBUG: CommitTransaction
>
> < 2018-11-21 11:12:19.484 UTC > DEBUG: name: unnamed; blockState:
> STARTED; state: INPROGR, xid/subid/cid: 151784/1/0 (used), nestlvl: 1,
> children:
>
> *< 2018-11-21 11:12:19.485 UTC > LOG: duration: 1.007 ms statement:
> alter database test rename to test_old*
>
> < 2018-11-21 11:12:19.485 UTC > DEBUG: shmem_exit(0): 1 before_shmem_exit
> callbacks to make
>
> < 2018-11-21 11:12:19.485 UTC > DEBUG: shmem_exit(0): 6 on_shmem_exit
> callbacks to make
>
> < 2018-11-21 11:12:19.485 UTC > DEBUG: proc_exit(0): 3 callbacks to make
>
> < 2018-11-21 11:12:19.485 UTC > DEBUG: exit(0)
>
> < 2018-11-21 11:12:19.485 UTC > DEBUG: shmem_exit(-1): 0
> before_shmem_exit callbacks to make
>
> < 2018-11-21 11:12:19.485 UTC > DEBUG: shmem_exit(-1): 0 on_shmem_exit
> callbacks to make
>
> < 2018-11-21 11:12:19.485 UTC > DEBUG: proc_exit(-1): 0 callbacks to make
>
> < 2018-11-21 11:12:19.486 UTC > DEBUG: reaping dead processes
>
> < 2018-11-21 11:12:19.486 UTC > DEBUG: server process (PID 14261) exited
> with exit code 0
>
>
>
>
>
> On Wed, Nov 21, 2018 at 2:50 PM Zwettler Markus (OIZ) <
> Markus(dot)Zwettler(at)zuerich(dot)ch> wrote:
>
> Hi,
>
> We have a postgresql cluster with two databases.
> We rename (switch) the databases daily (alter database x rename to y;)
> The query performance slows down after each renaming.
> Question: Why? Does postgresql flush the db-cache?
>
> Thanks,
> Markus
>
>