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

From: Thalis Kalfigkopoulos <tkalfigo(at)gmail(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Drop/Re-Creating database extremely slow + doesn't lose data
Date: 2016-05-31 13:49:26
Message-ID: CAEkCx9G3g-g79sMEfZHs0wVv11OX57OeMNaOKvxLthkeAb_tcg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Francisco Olarte 2016-05-31 13:50:25 Re: Switching roles as an replacement of connection pooling tools
Previous Message Francisco Olarte 2016-05-31 13:41:47 Re: Deleting a table file does not raise an error when the table is touched afterwards, why?