PostgreSQL database default location on Linux

Question

What is the default directory where PostgreSQL will keep all databases on Linux

User · Answer

Default in Debian 8 1 and PostgreSQL 9 4 after the installation with the package manager apt-get  ps auxw    grep postgres   grep -- -D postgres 17340  0 0  0 5 226700 21756          S    09 50   0 00  usr lib postgresql 9 4 bin postgres -D  var lib postgresql 9 4 main -c config file  etc postgresql 9 4 main postgresql conf   so apparently  var lib postgresql 9 4 main

User · Answer

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

User · Answer

Below query will help to find postgres configuration file   postgres   SHOW config file               config file -------------------------------------   var lib pgsql data postgresql conf  1 row    root node1 usr   cd  var lib pgsql data   root node1 data   ls -lrth total 48K -rw------- 1 postgres postgres    4 Nov 25 13 58 PG VERSION drwx------ 2 postgres postgres    6 Nov 25 13 58 pg twophase drwx------ 2 postgres postgres    6 Nov 25 13 58 pg tblspc drwx------ 2 postgres postgres    6 Nov 25 13 58 pg snapshots drwx------ 2 postgres postgres    6 Nov 25 13 58 pg serial drwx------ 4 postgres postgres   36 Nov 25 13 58 pg multixact -rw------- 1 postgres postgres  20K Nov 25 13 58 postgresql conf -rw------- 1 postgres postgres 1 6K Nov 25 13 58 pg ident conf -rw------- 1 postgres postgres 4 2K Nov 25 13 58 pg hba conf drwx------ 3 postgres postgres   60 Nov 25 13 58 pg xlog drwx------ 2 postgres postgres   18 Nov 25 13 58 pg subtrans drwx------ 2 postgres postgres   18 Nov 25 13 58 pg clog drwx------ 5 postgres postgres   41 Nov 25 13 58 base -rw------- 1 postgres postgres   92 Nov 25 14 00 postmaster pid drwx------ 2 postgres postgres   18 Nov 25 14 00 pg notify -rw------- 1 postgres postgres   57 Nov 25 14 00 postmaster opts drwx------ 2 postgres postgres   32 Nov 25 14 00 pg log drwx------ 2 postgres postgres 4 0K Nov 25 14 00 global drwx------ 2 postgres postgres   25 Nov 25 14 20 pg stat tmp

User · Answer

The command pg lsclusters  at least under Linux   Ubuntu  can be used to list the existing clusters and with it also the data directory   Ver Cluster Port Status Owner    Data directory               Log file 9 5 main    5433 down   postgres  var lib postgresql 9 5 main  var log postgresql postgresql-9 5-main log 10  main    5432 down   postgres  var lib postgresql 10 main   var log postgresql postgresql-10-main log

User · Answer

Connect to a database and execute the command   SHOW data directory    More information   https   www postgresql org docs current sql-show html https   www postgresql org docs current runtime-config-file-locations html

User · Answer

On Centos 6 5 PostgreSQL 9 3   Change the value of  PGDATA  var lib pgsql data  to whatever location you want in the initial script file  etc init d postgresql   Remember to  chmod 700  and chown postgres postgres to the new location and you re the boss

User · Answer

I think best method is to query pg setting view    select s name  s setting  s short desc from pg settings s where s name  data directory     Output         name               setting                      short desc ---------------- ------------------------ -----------------------------------  data directory    var lib pgsql 10 data   Sets the server s data directory   1 row

User · Answer

var lib postgresql  version  data    At least in Gentoo Linux and Ubuntu 14 04 by default   You can find postgresql conf and look at param data directory  If it is commented then database directory is the same as this config file directory

[linux] PostgreSQL database default location on Linux

Examples related to linux

Examples related to postgresql