4

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
  • 1
    "*i can't set PGDATA without cluster initialization*" that does not make sense. `PGDATA` is an **environment** variable. It doesn't require anything in order to be set. Just define it in your `.profile` or through a `set` command. –  Sep 24 '14 at 09:50
  • http://www.postgresql.org/docs/9.2/static/app-initdb.html <= in psql 9.2 you can set pgdata only use initdb, after that pgsql will controling by postgresql-xc. if you want set pgdata use text editor you get nothing... –  Sep 24 '14 at 10:16
  • Again: PGDATA is an ***environment*** variable. Unless you don't mean `PGDATA` but the actual *data directory*. Those are two different things (the variable `PGDATA` usually _points_ to the data directory that should be used by Postgres). Also: if you are using Postgres-XC you should have mentioned that from the beginning. Postgres-XC and PostgreSQL are two different things. –  Sep 24 '14 at 10:20
  • i'am add example...and yeah, i understood what is pgdata variable. But it's not work for me. When i use --pgdata=directory i can't take any data from psql –  Sep 24 '14 at 10:40
  • **Sharing a PostgreSQL data directory between two running PostgreSQL instances using a file system mounted on drbd is guaranteed to cause severe data corruption**. This is true even if it's a cluster file system, because PostgreSQL its self requires that only one PostgreSQL instance *ever* access the data directory at a time. Do not do this. If you attempt it you will completely destroy your database. – Craig Ringer Sep 24 '14 at 11:39
  • 1
    (You also keep on mentioning postgres-xc. Which is it, PostgreSQL, or postgres-xc? They're not the same thing.) – Craig Ringer Sep 24 '14 at 11:40

2 Answers2

6

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.

Daniel Vérité
  • 58,074
  • 15
  • 129
  • 156
4

Place the below in your ~/.bashrc file. 

PATH=$PATH:/usr/lib/postgresql/<PostgresVersionNumber>/bin
export PATH
export PGDATA="$HOME/<FolderNameForPostgresDatabases>"

Replace 'PostgresVersionNumber' with your installed Postgres version number.
Replace 'FolderNameForPostgresDatabases' with the name of the folder you want your postgres databases to reside. Make sure the folder is created

From a terminal, you have to navigate to ~/FolderNameForPostgresDatabases, then run:

initdb

Do that before trying to run:

pg_ctl start
Efe Ariaroo
  • 1,052
  • 10
  • 10