Postgresql Database Default Location on Linux

PostgreSQL database default location on Linux

The "directory where postgresql will keep all databases" (and configuration) is called "data directory" and corresponds to what PostgreSQL calls (a little confusingly) a "database cluster", which is not related to distributed computing, it just means a group of databases and related objects managed by a PostgreSQL server.

The location of the data directory depends on the distribution. If you install from source, the default is /usr/local/pgsql/data:

In file system terms, a database
cluster will be a single directory
under which all data will be stored.
We call this the data directory or
data area. It is completely up to you
where you choose to store your data.
There is no default, although
locations such as
/usr/local/pgsql/data or
/var/lib/pgsql/data are popular.
(ref)

Besides, an instance of a running PostgreSQL server is associated to one cluster; the location of its data directory can be passed to the server daemon ("postmaster" or "postgres") in the -D command line option, or by the PGDATA environment variable (usually in the scope of the running user, typically postgres). You can usually see the running server with something like this:

[root@server1 ~]# ps auxw |  grep postgres | grep -- -D
postgres  1535  0.0  0.1  39768  1584 ?        S    May17   0:23 /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data

Note that it is possible, though not very frequent, to run two instances of the same PostgreSQL server (same binaries, different processes) that serve different "clusters" (data directories). Of course, each instance would listen on its own TCP/IP port.

PostgreSQL data file location

select setting from pg_settings where name = 'data_directory'

and

ps auxw | grep postgres | grep -- -D 

both generate the same result. The first one through postgresql, second one command line. Thanks to @a_horse_with_no_name

Where Postgres database files are saved in ubuntu?

In the postgres prompt, just execute this query:

SHOW data_directory;

Check also the Ubuntu manual:
https://help.ubuntu.com/10.04/serverguide/C/postgresql.html

Where does PostgreSQL store configuration/conf files?

Or ask your database:

$ psql -U postgres -c 'SHOW config_file'

or, if logged in as the ubuntu user:

$ sudo -u postgres psql -c 'SHOW config_file'

How to move location of postrgresql 13 database

Assuming your configuration files are located under $PG_DATA, where they belong:

---

1. Shut down the (old) database
2. Copy the data directory to the new location (use cp -rp, or rsync -acv, or tar, or cpio, ...) Make sure that file attributes and ownership are preserved by the copy. The pgdata directory should be mode == 0600, and owner.group == postgres.postgres.
3. [optionally] rename the old data directory
4. [optionally] you may want to edit the configuration files at the new location
5. edit the startup file (in /etc/init.d/postgresql ) and make sure $PG_DATA points to the new location. [note: this is for ubuntu; other distributions may us a different starting mechanism]
6. Start the new database, and check if it runs (ps auxw| grep postgres, and if you can connect (psql -U postgres postgres)
7. [optionally] remove the directory tree at the old location.

---

Related Topics