Re: Drop/Re-Creating database extremely slow + doesn't lose data

From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Thalis Kalfigkopoulos
Sent: Tuesday, May 31, 2016 9:49 AM
To: pgsql-general(at)postgresql(dot)org
Subject: [GENERAL] Drop/Re-Creating database extremely slow + doesn't lose data

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(at)template1[[local]:5952] # vacuum full;
VACUUM
Time: 61292.151 ms
pgdba(at)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(at)template1[[local]:5952] # CREATE DATABASE dafodb;
CREATE DATABASE
Time: 35776.047 ms
pgdba(at)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(at)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(at)template1[[local]:5952] # \c dafodb
You are now connected to database "dafodb" as user "pgdba".
pgdba(at)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(at)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(at)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