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.
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? |