PostgreSQL set PGDATA variable

user4074041 picture user4074041 · Sep 24, 2014 · Viewed 24.2k times · Source

Server: Ubuntu server 14 lts + PostgreSQL 9.2 I want create cluster database using drbd, but i can't set PGDATA without cluster initialization. I just need say pgsql use data from drbd disk. How i can do it?

Example 1:

mkdir /cluster/var/lib/pgsql -p
chown postgres:postgres /cluster/var/lib/pgsql -R
cp -R /var/lib/pgsql /cluster/var/lib/pgsql

edit /etc/init.d/postgresql :
 PGDATA=/cluster/var/lib/pgsql/data
...
PGLOG=/cluster/var/lib/pgsql/pgstartup.log

/etc/init.d/postgresql start

in postgresql 8.3 it works, but in 9.2 i can't change pgdata in /etc/init.d/postgresql, i need find another file and set pgdata, but, surprise, it's do nothing.

Example 2: PGDATA - Specifies the directory where the database cluster is to be stored; can be overridden using the -D option.

Ok, let's start: --pgdata=directory yeah, it's works! but now we have postgresql-xc and error like "postgresql don't know this user - postgresql". drbd start replicate data from cluster, but postgresql start it too.

UPD 1:

root: initdb --pgdata=/home/username/dir
~initdb not install~bla-bla-bla~use apt-get install postgres-xc

UPD2:

$: /usr/lib/postgresql/9.3/bin/initdb --pgdata=/whateveryouwant

#now you can run postgresql only one way:

$: /usr/lib/postgresql/9.3/bin/postgres -D /see_up

#then:

LOG:  database system was shut down at 2014-09-26 15:56:33 YEKT   
LOG:  database system is ready to accept connections    
LOG:  autovacuum launcher started

#aaaaaaaaaaand...nothing. just empty console, ^C stopping postgres    
#another SSH connect:

$: ps-ela

S  1000  5995  5217  0  80   0 - 62202 poll_s pts/0    00:00:00 postgres    
1 S  1000  5997  5995  0  80   0 - 62202 poll_s ?        00:00:00 postgres    
1 S  1000  5998  5995  0  80   0 - 62202 poll_s ?        00:00:00 postgres    
1 S  1000  5999  5995  0  80   0 - 62202 poll_s ?        00:00:00 postgres    
1 S  1000  6000  5995  0  80   0 - 62415 poll_s ?        00:00:00 postgres    
1 S  1000  6001  5995  0  80   0 - 26121 poll_s ?        00:00:00 postgres

#is it ok? because...

$: /etc/init.d/postgresql status    
9.3/main (port 5432): down

Answer

Daniel Vérité picture Daniel Vérité · Sep 24, 2014

With Ubuntu, to use a specific data directory for a PostgreSQL instance, you just need to do:

# pg_createcluster --datadir=/path/to/the/directory 9.3 nameofcluster

You don't need or want to fiddle with $PGDATA anywhere. See pg_createcluster manpage.

If using drdb to replicate the data directory in a cluster, keep in mind that the term cluster in PostgreSQL parlance has a different signification: it's always a cluster of databases, which means all the databases served by a specific PostgreSQL instance with its unique data directory. This differs from a drdb cluster which is a cluster of machines.

The data directory of a PostgreSQL cluster cannot be shared at all, in the sense that only one specific instance of PostgreSQL running on a single machine may write into it, otherwise corruption will ensue.

If drdb is configured to conform with this requirement, it's OK to use it, otherwise not. You may also question why using drdb in the first place when PostgreSQL has built-in replication options since version 8.3 that are more specialized and better suited to a database than pure disk replication.