|
From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx]
On Behalf Of Thalis Kalfigkopoulos Intention: to drop a database and recreate it. Expectation: the newly created db should be empty What happens: dropping is fast, creation is slow, and when I reconnect, all the data objects are still there. Commands (tried both through command line with dropdb/createdb and through psql) pgdba@template1[[local]:5952] # vacuum full; VACUUM Time: 61292.151 ms pgdba@template1[[local]:5952] # \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+-------+----------+-------------+-------------+------------------- postgres | pgdba | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | pgdba | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/pgdba + | | | | | pgdba=CTc/pgdba template1 | pgdba | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/pgdba + | | | | | pgdba=CTc/pgdba (3 rows) pgdba@template1[[local]:5952] # CREATE DATABASE dafodb; CREATE DATABASE Time: 35776.047 ms pgdba@template1[[local]:5952] # And the corresponding lines from pg_log: 2016-05-31 15:29:46 CEST [4591]: user=pgdba,db=template1,app=psql,client=[local] LOG: statement: CREATE DATABASE dafodb; 2016-05-31 15:29:46 CEST [3470]: user=,db=,app=,client= LOG: checkpoint starting: immediate force wait flush-all 2016-05-31 15:29:46 CEST [3470]: user=,db=,app=,client= LOG: checkpoint complete: wrote 241 buffers (1.5%); 0 transaction log file(s) added, 0 removed, 0 recycled; write=0.002 s, sync=0.035 s, total=0.045 s; sync files=54, longest=0.003
s, average=0.000 s; distance=67120 kB, estimate=67120 kB 2016-05-31 15:29:52 CEST [4596]: user=,db=,app=,client= LOG: process 4596 still waiting for RowExclusiveLock on object 1 of class 1262 of database 0 after 1000.138 ms 2016-05-31 15:29:52 CEST [4596]: user=,db=,app=,client= DETAIL: Process holding the lock: 4591. Wait queue: 4596. 2016-05-31 15:30:22 CEST [3470]: user=,db=,app=,client= LOG: checkpoint starting: immediate force wait 2016-05-31 15:30:22 CEST [3470]: user=,db=,app=,client= LOG: checkpoint complete: wrote 0 buffers (0.0%); 0 transaction log file(s) added, 4 removed, 0 recycled; write=0.000 s, sync=0.000 s, total=0.004 s; sync files=0, longest=0.000 s,
average=0.000 s; distance=0 kB, estimate=60408 kB 2016-05-31 15:30:22 CEST [4591]: user=pgdba,db=template1,app=psql,client=[local] LOG: duration: 35775.909 ms 2016-05-31 15:30:22 CEST [4596]: user=,db=,app=,client= LOG: process 4596 acquired RowExclusiveLock on object 1 of class 1262 of database 0 after 31471.839 ms 2016-05-31 15:30:22 CEST [4596]: user=,db=,app=,client= LOG: automatic vacuum of table "template1.pg_catalog.pg_statistic": index scans: 1 pages: 0 removed, 54 remain, 0 skipped due to pins tuples: 108 removed, 724 remain, 0 are dead but not yet removable buffer usage: 106 hits, 39 misses, 62 dirtied avg read rate: 2.044 MB/s, avg write rate: 3.250 MB/s system usage: CPU 0.00s/0.00u sec elapsed 0.14 sec 2016-05-31 15:30:51 CEST [4614]: user=,db=,app=,client= LOG: automatic analyze of table "template1.pg_catalog.pg_shdepend" system usage: CPU 0.00s/0.00u sec elapsed 0.02 sec Then I continue to check the newly created database is there: pgdba@template1[[local]:5952] # \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+-------+----------+-------------+-------------+------------------- dafodb | pgdba | UTF8 | en_US.UTF-8 | en_US.UTF-8 | postgres | pgdba | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | pgdba | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/pgdba + | | | | | pgdba=CTc/pgdba template1 | pgdba | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/pgdba + | | | | | pgdba=CTc/pgdba (4 rows) pgdba@template1[[local]:5952] # \c dafodb You are now connected to database "dafodb" as user "pgdba". pgdba@dafodb[[local]:5952] # \d List of relations Schema | Name | Type | Owner --------+-------------------------------------------------------+----------+------- public | XXXXXXX | table | pgdba public | YYYYYYYYYYYYY | sequence | pgdba .... .... .... So all the data is still there. Connected processes at the moment: pgdba@dafodb[[local]:5952] # select * from pg_stat_activity ; datid | datname | pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_start | query_start | state_change
| waiting | state | backend_xid --------+---------+------+----------+---------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+---------+--------+------------ 418048 | dafodb | 4622 | 10 | pgdba | psql | (null) | (null) | -1 | 2016-05-31 15:33:16.371456+02 | 2016-05-31 15:34:27.080439+02 | 2016-05-31 15:34:27.080439+02 | 2016-05-31 15:34:27.080442+02
| f | active | (null) (1 row) Time: 1.072 ms pgdba@dafodb[[local]:5952] # select * from pg_locks ; locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath ------------+----------+----------+--------+--------+------------+---------------+---------+--------+----------+--------------------+------+-----------------+---------+---------- relation | 418048 | 11673 | (null) | (null) | (null) | (null) | (null) | (null) | (null) | 3/165 | 4622 | AccessShareLock | t | t virtualxid | (null) | (null) | (null) | (null) | 3/165 | (null) | (null) | (null) | (null) | 3/165 | 4622 | ExclusiveLock | t | t (2 rows) Even weirder, created a new DB with a completely unrelated name. Again "create database" took long time, but then connected to it and it has all the data from the "dafodb". Also tried: renaming dafodb to dafodb_OLD and again "create database dafodb". Both contain the same data. All this on Pg 9.5.2 on 64bit Ubuntu with 3.13.0-74-generic. Any idea what's going on or how to recover? BR, Thalis K.
Check your template db (whichever is used to create new db). Probably your unexpected table and sequence are coming from there. Regards, Igor Neyman |
