Postgresql linux database location

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'

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:

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

Where does PostgreSQL store configuration/conf files?

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

Источник

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.

Источник

Where are PostgreSQL databases stored on my computer?

So this might be a stupid question, but I’m really new to this. So I’m making a Postgres database on the postgres server, that I started up with this command:

Which supposedly created the database. But where is this database? Where can I find it on my computer?

I recommend starting with postgresql.org/docs/current/static/tutorial.html . And you didn’t start PostgreSQL with the psql command, you connected to it; it’s a server and psql is the client.

The «database» is not a single file that you can take with you or look at. A database server manages this differently than e.g. Microsoft Access.

5 Answers 5

Where exactly your data is stored depends on how you installed and configured PostgreSQL. Running the

command as the postgres user (from inside the psql tool or another client) will tell you where it is though. For example, if you installed it from the debian or ubuntu repositories you would probably find your data files are somewhere like /var/lib/postgresql/9.2/main.

There probably isn’t anything terribly interesting in there though. Sometimes there are a few configuration files like pg_hba.conf (the windows version puts them there at least) but if you’re on debian you’ll find those under /etc/postgresql/.

If you’re expecting your database to be a single file that you can move around between systems, etc, you’re probably better off using a simpler embedded database like sqlite.

Источник

How to change PostgreSQL’s data directory on Linux

There comes a time when you have to restore a relatively large database locally. Most likely, you’ve partitioned your disk, and your root partition got the thick end of it, 50 GB if you were generous. Let’s assume that that’s not nearly enough for the database you’re about to restore. At the same time, your /home partition got the rest of the disk space you had available. You can always resize these two partitions, but then you would have to back up your /home directory, unmount it or find a Live USB and tamper with them. If you don’t have the time, or the desire, or even a backup disc, you can always change the location where postgresql stores its data. The following instructions are a love letter to all those lost souls who find themselves in this situation and forget to check the status of SELinux, as well as to my future self who’ll most likely have to do it again. Considering this is mostly a dump of my bash history, I hope this exact procedure works for you. If not, feel free to contact me and we’ll update it together.

Procedure

A fresh install is the easiest to change, but let’s assume you have some databases locally you don’t want to lose, just move them and restore the large database next to them. The steps are more or less the same anyway.

data_directory and config_file

$ sudo su - postgres [postgres@host ~]$ psql Password for user postgres: psql (12.6) Type "help" for help. postgres=# SHOW config_file; config_file ----------------------------------- /var/lib/pgsql/data/postgresql.conf (1 row) postgres=# SHOW data_directory; data_directory ------------------- /var/lib/pgsql/data (1 row) 

Stop the systemd service

systemctl stop postgresql.service 

Источник