Postgres Pro
Downloads
Home   >   mailing lists

Thread: iso-8859-1 database folders in base directory

iso-8859-1 database folders in base directory

From
Adarsh Sharma
Date:
Hi,

I was checking one disk space issue in PostgreSQL server and noticed that there are some folders which are not mapping to databases. I am not able to link those folders with any database oid. Can we safely delete those folders after shutting down the postgres? It is recommended to delete any files and directories in database directory but I need to clean 

postgres=# select oid,datname from pg_database;

    oid     |       datname

------------+----------------------

          1 | template1

      12998 | template0

      13003 | postgres

 1360816673 | demo1

  876257363 | demo2

  568075126 | db01

 2888078676 | app05_06

 2893858702 | app10

 2858172588 | app03

 2911598776 | demo_auto

 2911908415 | production_auto


-bash-4.1$ pwd

/var/lib/pgsql/9.4/data/base

-bash-4.1$ ls -ltr

total 12716

drwx------ 2 postgres postgres    4096 Feb 19  2016 12998

drwx------ 2 postgres postgres   12288 Feb 28 21:00 1

drwx------ 2 postgres postgres  163840 Mar 19 18:18 2567078690

drwx------ 2 postgres postgres  237568 Mar 19 19:10 2567168798

drwx------ 2 postgres postgres  151552 Mar 19 19:19 2567176994

drwx------ 2 postgres postgres  172032 Mar 19 19:34 2567193375

drwx------ 2 postgres postgres   12288 Mar 19 19:34 13003

drwx------ 2 postgres postgres  110592 Apr  1 14:58 568075126

drwx------ 2 postgres postgres   73728 Apr 25 21:28 876257363

drwx------ 2 postgres postgres    4096 May  4 02:24 1360816673

drwx------ 2 postgres postgres  536576 Jun  7 02:28 2888078676

drwx------ 2 postgres postgres   81920 Jun  7 04:14 2911598776

drwx------ 2 postgres postgres  573440 Jun  7 07:10 2858172588

drwx------ 2 postgres postgres  258048 Jun  7 07:40 2911908415

drwx------ 2 postgres postgres 9859072 Jun  7 08:14 pgsql_tmp

drwx------ 2 postgres postgres  720896 Jun  7 08:35 2893858702

-bash-4.1$ 


-bash-4.1$ du -sh *

6.6M    1

6.6M    12998

126M    13003

6.7M    1360816673

42G     2567078690

136G    2567168798

84G     2567176994

96G     2567193375

82G     2858172588

83G     2888078676

83G     2893858702

12G     2911598776

276G    2911908415

59G     568075126

3.2G    876257363

19G     pgsql_tmp



postgres=# select version();

                                                    version                                                    

---------------------------------------------------------------------------------------------------------------

 PostgreSQL 9.4.5 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-16), 64-bit

(1 row)


postgres=# \! cat /etc/issue

CentOS release 6.7 (Final)

Kernel \r on an \m



Cheers,

Adarsh Sharma


Re: iso-8859-1 database folders in base directory

From
Christoph Berg
Date:
Re: Adarsh Sharma 2018-06-07 <CAGx-QqKyW-UuMTw9Vw9uCX_Af+8GhS1HdkNLt6uV9DnxQGV0eg@mail.gmail.com>
> *drwx------ 2 postgres postgres  163840 Mar 19 18:18 2567078690*
> *drwx------ 2 postgres postgres  237568 Mar 19 19:10 2567168798*
> *drwx------ 2 postgres postgres  151552 Mar 19 19:19 2567176994*
> *drwx------ 2 postgres postgres  172032 Mar 19 19:34 2567193375*

These have all similar timestamps. Were you repeatedly trying to
create new databases at that time, but ran out of disk space and
PostgreSQL was panicing? That would explain the issue.

You can safely remove old database directories that are not referenced
in pg_database - you don't even need to shut down first, as PostgreSQL
doesn't know about the files anymore.

Christoph