|
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@xxxxxxxxx>
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.Zwettler@xxxxxxxxxx> wrote:
|
