I'd generally just search some directory inside /var, which might be quite obvious, but nothing will be better than reviewing the configuration that Postgresql is considering for its internal management. We don't want to do things in a remaining folder from a previous installation, so better know where we are. Plus, it's a safe way to access configuration files, independently of which distro or OS are you using. In my case, I want to know: where are the databases and tables inside the filesystem?
These are my databases:
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+------------+------------+-----------------------
instagram | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
Postgresql run-time configuration can be inspected via SHOW command on a PSQL shell. The configuration that matters here is data_directory:
postgres=# SHOW data_directory;
data_directory
--------------------------
/var/lib/postgresql/data
(1 row)
Since I'm using Postgresql in a Docker container, I'll spawn a shell inside the container in order to navigate through the filesystem:
~ $ docker ps | grep lab_postgres 24999bd8c9ef postgres "docker-entrypoint.s…" 2 months ago Up 5 hours 5432/tcp lab_postgres ~ $ docker exec -it lab_postgres bash root@24999bd8c9ef:/# cd /var/lib/postgresql/data root@24999bd8c9ef:/var/lib/postgresql/data# ls -l total 124 drwx------ 6 postgres postgres 4096 Jan 29 20:23 base drwx------ 2 postgres postgres 4096 Jan 29 20:28 global drwx------ 2 postgres postgres 4096 Nov 28 19:21 pg_commit_ts drwx------ 2 postgres postgres 4096 Nov 28 19:21 pg_dynshmem -rw------- 1 postgres postgres 4782 Nov 28 19:21 pg_hba.conf -rw------- 1 postgres postgres 1636 Nov 28 19:21 pg_ident.conf drwx------ 4 postgres postgres 4096 Jan 29 20:43 pg_logical drwx------ 4 postgres postgres 4096 Nov 28 19:21 pg_multixact drwx------ 2 postgres postgres 4096 Nov 28 19:21 pg_notify drwx------ 2 postgres postgres 4096 Nov 28 19:21 pg_replslot drwx------ 2 postgres postgres 4096 Nov 28 19:21 pg_serial drwx------ 2 postgres postgres 4096 Nov 28 19:21 pg_snapshots drwx------ 2 postgres postgres 4096 Jan 29 20:18 pg_stat drwx------ 2 postgres postgres 4096 Jan 30 01:26 pg_stat_tmp drwx------ 2 postgres postgres 4096 Nov 28 19:21 pg_subtrans drwx------ 2 postgres postgres 4096 Nov 28 19:21 pg_tblspc drwx------ 2 postgres postgres 4096 Nov 28 19:21 pg_twophase -rw------- 1 postgres postgres 3 Nov 28 19:21 PG_VERSION drwx------ 3 postgres postgres 4096 Jan 29 20:40 pg_wal drwx------ 2 postgres postgres 4096 Nov 28 19:21 pg_xact -rw------- 1 postgres postgres 88 Nov 28 19:21 postgresql.auto.conf -rw------- 1 postgres postgres 28085 Nov 28 19:21 postgresql.conf -rw------- 1 postgres postgres 36 Jan 29 20:18 postmaster.opts -rw------- 1 postgres postgres 94 Jan 29 20:18 postmaster.pid
There are config files and directories, including a particular directory called "base":
root@24999bd8c9ef:/var/lib/postgresql/data# cd base/ root@24999bd8c9ef:/var/lib/postgresql/data/base# ls -l total 32 drwx------ 2 postgres postgres 4096 Nov 28 19:21 1 drwx------ 2 postgres postgres 4096 Nov 28 19:21 13394 drwx------ 2 postgres postgres 12288 Jan 29 20:27 13395 drwx------ 2 postgres postgres 12288 Jan 29 20:36 16736
These directories are listed on the system catalog pg_database.
The numbers are OIDs for the available databases:
oid | datname | datdba | encoding | datcollate | datctype | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid | datminmxid | dattablespace | datacl
-------+-----------+--------+----------+------------+------------+---------------+--------------+--------------+---------------+--------------+------------+---------------+-------------------------------------
1 | template1 | 10 | 6 | en_US.utf8 | en_US.utf8 | t | t | -1 | 13394 | 479 | 1 | 1663 | {=c/postgres,postgres=CTc/postgres}
13394 | template0 | 10 | 6 | en_US.utf8 | en_US.utf8 | t | f | -1 | 13394 | 479 | 1 | 1663 | {=c/postgres,postgres=CTc/postgres}
13395 | postgres | 10 | 6 | en_US.utf8 | en_US.utf8 | f | t | -1 | 13394 | 479 | 1 | 1663 |
16736 | instagram | 10 | 6 | en_US.utf8 | en_US.utf8 | f | t | -1 | 13394 | 479 | 1 | 1663 |
(4 rows)
Let's enter on the directory that corresponds to the postgres database:
root@24999bd8c9ef:/var/lib/postgresql/data/base# cd 16736/ root@24999bd8c9ef:/var/lib/postgresql/data/base/16736# ls 112 16739_vm 16871_fsm 2607_vm 2656 2704 3351 3601 4160 113 16746 16871_vm 2608 2657 2753 3379 3601_fsm 4161 1247 16748 16875 2608_fsm 2658 2753_fsm 3380 3601_vm 4162 1247_fsm 16749 16877 2608_vm 2659 2753_vm 3381 3602 4163 1247_vm 16751 16889 2609 2660 2754 3394 3602_fsm 4164 1249 16753 16891 2609_fsm 2661 2755 3394_fsm 3602_vm 4165 1249_fsm 16753_fsm 16891_fsm 2609_vm 2662 2756 3394_vm 3603 4166 1249_vm 16753_vm 16891_vm 2610 2663 2757 3395 3603_fsm 4167 1255 16761 16896 2610_fsm 2664 2830 3429 3603_vm 4168 1255_fsm 16768 16898 2610_vm 2665 2831 3430 3604 4169 1255_vm 16770 174 2611 2666 2832 3431 3605 4170 1259 16770_fsm 175 2612 2667 2833 3433 3606 4171 1259_fsm 16770_vm 2187 2612_fsm 2668 2834 3439 3607 4172 1259_vm 16776 2224 2612_vm 2669 2835 3440 3608 4173 13245 16788 2328 2613 2670 2836 3455 3609 4174 13245_fsm 16790 2336 2615 2673 2837 3456 3712 5002 13245_vm 16790_fsm 2337 2615_fsm 2674 2838 3456_fsm 3764 548 13247 16790_vm 2579 2615_vm 2675 2838_fsm 3456_vm 3764_fsm 549 13249 16796 2600 2616 2678 2838_vm 3466 3764_vm 6102 13250 16798 2600_fsm 2616_fsm 2679 2839 3467 3766 6104 13250_fsm 16815 2600_vm 2616_vm 2680 2840 3468 3767 6106 13250_vm 16817 2601 2617 2681 2840_fsm 3501 3997 6110 13252 16817_fsm 2601_fsm 2617_fsm 2682 2840_vm 3502 4143 6111 13254 16817_vm 2601_vm 2617_vm 2683 2841 3503 4144 6112 13255 16823 2602 2618 2684 2995 3534 4145 6113 13255_fsm 16825 2602_fsm 2618_fsm 2685 2996 3541 4146 6117 13255_vm 16837 2602_vm 2618_vm 2686 3079 3541_fsm 4147 826 13257 16839 2603 2619 2687 3079_fsm 3541_vm 4148 827 13259 16839_fsm 2603_fsm 2619_fsm 2688 3079_vm 3542 4149 828 13260 16839_vm 2603_vm 2619_vm 2689 3080 3574 4150 pg_filenode.map 13260_fsm 16844 2604 2620 2690 3081 3575 4151 pg_internal.init 13260_vm 16846 2605 2620_fsm 2691 3085 3576 4152 PG_VERSION 13262 16858 2605_fsm 2620_vm 2692 3118 3596 4153 13264 16860 2605_vm 2650 2693 3119 3597 4154 1417 16860_fsm 2606 2651 2696 3164 3598 4155 1418 16865 2606_fsm 2652 2699 3256 3599 4156 16737 16867 2606_vm 2653 2701 3257 3600 4157 16739 16869 2607 2654 2702 3258 3600_fsm 4158 16739_fsm 16871 2607_fsm 2655 2703 3350 3600_vm 4159
There are a lot of files and all these numbers are (again) OIDs listed in another system catalog called pg_class. The files that have just a number on its name, are the pure tables inside the postgres database (16736).
For example, from these files, which one could be the one that is the table pg_user? Let's find out:
postgres=# SELECT oid, relname FROM pg_class WHERE relname = 'pg_user'; 12098 | pg_user
Let's inspect this table:
postgres=# \d pg_user; usename | name | | | usesysid | oid | | | usecreatedb | boolean | | | usesuper | boolean | | | userepl | boolean | | | usebypassrls | boolean | | | passwd | text | | | valuntil | timestamp with time zone | | | useconfig | text[] | C | | postgres=# SELECT * FROM pg_user; postgres|10 | t | t | t | t | ******** | |
Indeed, is the table that holds user data from Postgres. I don't think I'm going to use this knowledge that much, to be honest. But at least, from now and on, I can have an idea of where all Postgres data is stored and how Postgres catalogs the data for its internal management. One more lesson learned :).