Home / server / Questions / 798518
Accepted
fadedbee
Asked: 2016-08-24 00:26:37 +0800 CST

Where does postgres store the data on Ubuntu?

  • 772

I have a broken server, and backups of /etc/postgresql and /var/lib/postgresql.

The database I am looking for was used by a 2009 release of Redmine, if that makes any difference. https://www.redmine.org/

The backup of /etc/postgresql/postgresql.conf says:

data_directory = '/var/lib/postgresql/8.3/main'         # use data in another directory

Listing the backup of /var/lib/postgresql/8.3/main I see:

drwx------ 6 mysql scanner 4096 Nov 24  2009 base
drwx------ 2 mysql scanner 4096 Aug 15 07:40 global
drwx------ 2 mysql scanner 4096 Nov 23  2009 pg_clog
drwx------ 4 mysql scanner 4096 Nov 23  2009 pg_multixact
drwx------ 2 mysql scanner 4096 Nov 23  2009 pg_subtrans
drwx------ 2 mysql scanner 4096 Nov 23  2009 pg_tblspc
drwx------ 2 mysql scanner 4096 Nov 23  2009 pg_twophase
-rw------- 1 mysql scanner    4 Nov 23  2009 PG_VERSION
drwx------ 3 mysql scanner 4096 May 24 10:46 pg_xlog
-rw------- 1 mysql scanner  133 Jun 25  2014 postmaster.opts
-rw------- 1 mysql scanner   54 Jun 25  2014 postmaster.pid

(Ignore the ownership/group, they were messed up by the backup procedure.)

My concern is that only one file has an up-to-date modification time:

$ find . -mtime -10
./global
./global/pgstat.stat

Does this mean that the database data was written somewhere else, rather than into /var/lib/postgresql?

There does seem to be some significant disk usage:

$ du .
12  ./pg_clog
4   ./pg_twophase
12  ./pg_multixact/members
12  ./pg_multixact/offsets
28  ./pg_multixact
4   ./pg_tblspc
76  ./pg_subtrans
4   ./pg_xlog/archive_status
98312   ./pg_xlog
300 ./global
25428   ./base/16427
4280    ./base/11510
4500    ./base/11511
4280    ./base/1
38492   ./base
137244  .

Does this imply a non-empty database?

postgresql

2 Answers

  1. If there have been no changes to the contents of the directories (no files created/deleted etc) then the directory timestamps won't change. This could account for many of the old timestamps - the DB was initialised in 2009 and had basically just worked since.

    The two files from 2014 ... my guess is that someone made changes to the options in the postmaster.opts file and restarted the server. It then ran until it stopped some time after May 24th 2016.

    Where does postgres store the data on ubuntu

    In the location identified by data_directory.

    Does this mean that the database data was written somewhere else, rather than into /var/lib/postgresql?

    No

    Does this imply a non-empty database?

    Yes

    • 1
  2. Best Answer

    My confusion was caused by the fact that no users had entered any new data in the last 10 days. When I looked to for modified files in the last 20 days, there were many.

    $ find . -type f -mtime -20 -exec ls -lA {} \;
-rw------- 1 mysql scanner 8192 Aug 12 11:11 ./pg_clog/0000
-rw------- 1 mysql scanner 16777216 Aug 12 11:11 ./pg_xlog/000000010000000000000009
-rw------- 1 mysql scanner 32526 Aug 15 07:40 ./global/pgstat.stat
-rw------- 1 mysql scanner 8192 Aug 12 11:11 ./global/pg_control
-rw------- 1 mysql scanner 335872 Aug 11 11:11 ./base/16427/16615
-rw------- 1 mysql scanner 90112 Aug 11 11:11 ./base/16427/16963
-rw------- 1 mysql scanner 57344 Aug 10 11:31 ./base/16427/16969
-rw------- 1 mysql scanner 131072 Aug 11 11:11 ./base/16427/16964
-rw------- 1 mysql scanner 90112 Aug 11 11:11 ./base/16427/16908
-rw------- 1 mysql scanner 8192 Aug  9 11:56 ./base/16427/16485
-rw------- 1 mysql scanner 729088 Aug 12 11:11 ./base/16427/16588
-rw------- 1 mysql scanner 16384 Aug 11 11:11 ./base/16427/16428
-rw------- 1 mysql scanner 81920 Aug 11 11:11 ./base/16427/16910
-rw------- 1 mysql scanner 73728 Aug 12 11:11 ./base/16427/16962
-rw------- 1 mysql scanner 8192 Aug 10 11:31 ./base/16427/16731
-rw------- 1 mysql scanner 8192 Aug 12 09:31 ./base/16427/16760
-rw------- 1 mysql scanner 155648 Aug  5 11:51 ./base/16427/2619
-rw------- 1 mysql scanner 212992 Aug 11 11:11 ./base/16427/16604
-rw------- 1 mysql scanner 73728 Aug 10 11:31 ./base/16427/16970
-rw------- 1 mysql scanner 8192 Aug 12 11:11 ./base/16427/16602
-rw------- 1 mysql scanner 8192 Aug  9 11:56 ./base/16427/16477
-rw------- 1 mysql scanner 8192 Aug 11 16:11 ./base/16427/16626
-rw------- 1 mysql scanner 57344 Aug 12 11:11 ./base/16427/16906
-rw------- 1 mysql scanner 57344 Aug 10 11:31 ./base/16427/16928
-rw------- 1 mysql scanner 212992 Aug 10 11:31 ./base/16427/16728
-rw------- 1 mysql scanner 16384 Aug 11 16:11 ./base/16427/16912
-rw------- 1 mysql scanner 8192 Aug 11 11:11 ./base/16427/16624
-rw------- 1 mysql scanner 16384 Aug 11 11:11 ./base/16427/16878
-rw------- 1 mysql scanner 1155072 Aug  9 11:56 ./base/16427/16479
-rw------- 1 mysql scanner 8192 Aug 11 11:11 ./base/16427/16443
-rw------- 1 mysql scanner 8192 Aug 11 11:11 ./base/16427/16613
-rw------- 1 mysql scanner 8192 Aug 11 16:11 ./base/16427/16633
-rw------- 1 mysql scanner 204800 Aug  9 11:56 ./base/16427/16886
-rw------- 1 mysql scanner 10633216 Aug 10 09:51 ./base/16427/16469
-rw------- 1 mysql scanner 2334720 Aug  9 11:56 ./base/16427/16955
-rw------- 1 mysql scanner 466944 Aug  9 11:56 ./base/16427/16957
-rw------- 1 mysql scanner 2064384 Aug  9 11:56 ./base/16427/16884
    • 0