recovery.conf in pgdata not data directory.

[blogh]

Hi,

I think we should create the new recovery.conf in the data directory not PGDATA.

https://www.postgresql.org/docs/9.4/static/continuous-archiving.html
Create a recovery command file recovery.conf in the cluster data directory (see Chapter 26).

postgresql.conf

# The default values of these variables are driven from the -D command-line
# option or PGDATA environment variable, represented here as ConfigDir.

#data_directory = 'ConfigDir'           # use data in another directory
                                        # (change requires restart)

Benoit

[ioguix]

Hi,

I'm not sure to understand the difference here.

pgdata as we set it up in PAF must be the root of the instance. Whatever it is designed in data_directory or not, many different checks are related to this root: the postrgesql.pid existance, PGVERSION, the controldata file, etc.

The recovery.conf file MUST be created in the folder, whatever the value of this parameter.

[blogh]

We also start postgres with the same value of pgdata.

pgdata in pg_ctl start -D $PGDATA refers to the config directory (where postgresql.conf is).
pgdata in the other cases refers to the data_directory (where the pid and PGVERSION are).

I could set up my cluster like this:

postgres@x64lmwbio9e:/data01/postgres/bast01$ ls -al
total 24
drwxr-xr-x  6 root     root     4096 Jun 23 17:55 .
drwxr-xr-x  3 root     root     4096 Mar 31 11:21 ..
drwx------  5 postgres postgres 4096 Apr 11 15:20 btbs001 => DB
drwx------  2 postgres postgres 4096 Jun 23 17:56 conf => Config postgresql.conf pg_hba.conf pg_ident.conf
drwx------  3 postgres postgres 4096 Jun 23 17:56 pg_xlog 
drwx------ 18 postgres postgres 4096 Jun 23 17:57 systeme => recovery.conf PG_VERSION etc..

postgres@x64lmwbio9e:/data01/postgres/bast01$ ps -ef |grep postgres
root     28116 28114  0 17:52 pts/0    00:00:00 sudo su - postgres
root     28117 28116  0 17:52 pts/0    00:00:00 su - postgres
postgres 28118 28117  0 17:52 pts/0    00:00:00 -su
postgres 28247     1  1 17:57 pts/0    00:00:00 /usr/lib/postgresql/9.4/bin/postgres -D /data01/postgres/bast01/conf
postgres 28248 28247  0 17:57 ?        00:00:00 postgres: logger process
postgres 28250 28247  0 17:57 ?        00:00:00 postgres: checkpointer process
postgres 28251 28247  0 17:57 ?        00:00:00 postgres: writer process
postgres 28252 28247  0 17:57 ?        00:00:00 postgres: wal writer process
postgres 28253 28247  0 17:57 ?        00:00:00 postgres: autovacuum launcher process
postgres 28254 28247  0 17:57 ?        00:00:00 postgres: archiver process
postgres 28255 28247  0 17:57 ?        00:00:00 postgres: stats collector process
postgres 28257 28118  0 17:57 pts/0    00:00:00 ps -ef
postgres 28258 28118  0 17:57 pts/0    00:00:00 grep postgres

postgres@x64lmwbio9e:/data01/postgres/bast01$ psql -p 5433 -xc "SELECT name,setting FROM pg_settings WHERE name='data_directory';"
-[ RECORD 1 ]----------------------------
name    | data_directory
setting | /data01/postgres/bast01/systeme

EDIT: I modified one of my cluster for a better exemple.

[ioguix]

Hi,

Ok, well, this is an exotic way to setup PostgreSQL, first time I see this :)

Moreover, that make me wonder why they decided to put the recovery.conf file in data_directory instead of PGDATA with of conf files...

I gave it some minutes yesterday to try to create an instance this way, but when I tried to start it, it complained it couldn't find the value of parameter "port" with the following command:

postgres -C port -D /path/to/conf

Could you provide me with some quick steps to reproduce your test case?

Thank you,

[blogh]

Hi,

I was shocked when I discovered this too... Especially the first time I tried to switchover and postgres wouldn't find my recovery.conf :).

The setup is done like this:

• I put postgresql.conf, pg_hba.conf and pg_ident.conf in the conf directory.
• I fill the data_directory GUC with the correct value.
• And I start the cluster (usually with pg_ctl but postgres -c "port=5434" -D .... works for me too)

Benoit.

On a side note I wonder where postgres will put the postgresql.auto.conf. I have to check this.

UPDATE: I miss read you message. But postgres -C works for me..
postgres@x64lmwbio9e:~$ /usr/lib/postgresql/9.4/bin/postgres -C port -D /data01/postgres/bast01/conf
5433

[blogh]

Hi,

This setup is by default on Ubuntu. (and Debian ?)

Would you be interested, if I modify (pgsqlms & README.md) to do the following ?

• Add a DATADIR parameter (<=> GUC data_directory). the parameter will default to the value of pgdata.
• Replace pgdata by datadir when needed.
• I noticed a bad comment on the PG_VERSION test (pgsql_validate_all)
• I noticed a --pgdata in one of the pg_ctl (since you use -D in all other cases, I propose to change it also). (pgsql_demote)

Benoit.

[ioguix]

Hello Benoit,

Patch are welcome !

But first, before you start working on this, I need to understand...especially because I still wasn't able to reproduce this. Here is my setup:

$ pwd
/home/ioguix/var/lib/pgsql/test

$ ls
conf  data

$ ls conf
pg_hba.conf  pg_ident.conf  postgresql.conf

$ ls data
base  global  pg_clog  pg_multixact  pg_notify  pg_serial  pg_snapshots  pg_stat  pg_stat_tmp  pg_subtrans  pg_tblspc  pg_twophase  PG_VERSION  pg_xlog

$ grep data_directory conf/postgresql.conf 
data_directory = '/home/ioguix/var/lib/pgsql/test/data'     # use data in another directory

$ grep ^port conf/postgresql.conf 
port = 5435

$ pg_ctl -D /home/ioguix/var/lib/pgsql/test/conf status
FATAL:  invalid value for parameter "port": ""
pg_ctl: could not determine the data directory using command ""/home/ioguix/usr/local/pgsql/REL9_3_12/bin/postgres" -C data_directory -D "/home/ioguix/var/lib/pgsql/test/conf" "

Moreover, as far as I know, Debian/Ubuntu does not setup cluster this way. They put the conf files (and some additional ones) in /etc/postgresql/<major_version>/<name>/ and everything else in /var/lib/postgresql/<major_version>/<name>/. They start the cluster using the config_file GUC in argument to specify where is the postgresql.conf file. Eg.:

$ ls /etc/postgresql/9.4/main/
environment  pg_ctl.conf  pg_hba.conf  pg_ident.conf  postgresql.conf  start.conf

$ sudo pg_ctlcluster 9.4/main start

$ pg_lsclusters 
Ver Cluster Port Status Owner    Data directory               Log file
9.4 main    5432 online postgres /var/lib/postgresql/9.4/main /var/log/postgresql/postgresql-9.4-main.log

$ ps f -u postgres
  PID TTY      STAT   TIME COMMAND
19749 ?        S      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
19751 ?        Ss     0:00  \_ postgres: checkpointer process                                                                                              
19752 ?        Ss     0:00  \_ postgres: writer process                                                                                                    
19753 ?        Ss     0:00  \_ postgres: wal writer process                                                                                                
19754 ?        Ss     0:00  \_ postgres: autovacuum launcher process                                                                                       
19755 ?        Ss     0:00  \_ postgres: stats collector process

[ioguix]

• I noticed a bad comment on the PG_VERSION test (pgsql_validate_all)
• I noticed a --pgdata in one of the pg_ctl (since you use -D in all other cases, I propose to change it also). (pgsql_demote)

About this, you can make a pull request whenever you can, I'll review it quickly, I promise :)
However, note that I prefer long arguments over short one when possible. So I would prefer --pgdata over -D.

I'll take care of these two patches if I find some time in one or two weeks and you couldn't do it yet.

Thanks!

[blogh]

Hi,

For the --pgdata / -D, I prefer long arguments also. I opted for the lazy solution.
I am not very familiar with the github thing. It's a good occasion to do this.

You are right for the debian thing. I realised it this weekend while testing stuff. Sorry.

Here is my setup, tell me if you need more infos. How do you start your cluster ? (postmaster.opts):

postgres@x64lmwbio9e:/data01/postgres/bast01$ ls -al
total 24
drwxr-xr-x  6 root     root     4096 Jun 23 17:55 .
drwxr-xr-x  3 root     root     4096 Mar 31 11:21 ..
drwx------  5 postgres postgres 4096 Apr 11 15:20 btbs001
drwx------  2 postgres postgres 4096 Jul  1 10:37 conf
drwx------  3 postgres postgres 4096 Aug 22 12:48 pg_xlog
drwx------ 18 postgres postgres 4096 Aug 19 16:47 systeme

postgres@x64lmwbio9e:/data01/postgres/bast01$ ls ./conf/
pg_hba.conf  pg_ident.conf  postgresql.conf

postgres@x64lmwbio9e:/data01/postgres/bast01$ ls ./systeme/
base     pg_dynshmem                  pg_ident.conf                  pg_logical    pg_replslot   pg_stat      pg_tblspc    pg_xlog                          postgresql.conf.RENAME
global   pg_hba.conf                  pg_ident.conf_20160411_151519  pg_multixact  pg_serial     pg_stat_tmp  pg_twophase  postgresql.auto.conf             postmaster.opts
pg_clog  pg_hba.conf_20160411_151519  pg_log                         pg_notify     pg_snapshots  pg_subtrans  PG_VERSION   postgresql.conf_20160411_151519  postmaster.pid

Config:

postgres@x64lmwbio9e:/data01/postgres/bast01$ grep "_directory" ./conf/postgresql.conf
data_directory = '/data01/postgres/bast01/systeme'              # use data in another directory
#unix_socket_directory = ''             # (change requires restart)
#log_directory = 'pg_log'               # directory where log files are written,
log_directory = '/traces01/postgres/bast01/pg_log'

Start / tests:

postgres@x64lmwbio9e:/data01/postgres/bast01$ /usr/lib/postgresql/9.4/bin/pg_ctl start -D /data01/postgres/bast01/conf -w
waiting for server to start....2016-08-22 14:03:34 CEST [db=][user=][PID=19325][host=] LOG:  redirecting log output to logging collector process
2016-08-22 14:03:34 CEST [db=][user=][PID=19325][host=] HINT:  Future log output will appear in directory "/traces01/postgres/bast01/pg_log".
 done
server started

postgres@x64lmwbio9e:/data01/postgres/bast01$ /usr/lib/postgresql/9.4/bin/pg_ctl status -D /data01/postgres/bast01/conf
pg_ctl: server is running (PID: 19325)
/usr/lib/postgresql/9.4/bin/postgres "-D" "/data01/postgres/bast01/conf"

postgres@x64lmwbio9e:/data01/postgres/bast01$ /usr/lib/postgresql/9.4/bin/postgres -D /data01/postgres/bast01/conf -C port
5433

postgres@x64lmwbio9e:/data01/postgres/bast01/systeme$ /usr/lib/postgresql/9.4/bin/postgres -D /data01/postgres/bast01/conf -C data_directory
/data01/postgres/bast01/systeme

[ioguix]

OK, I found my problem, just some issues with my environment.

Anyway, if you have some time to produce a patch to add this datadir (in minuscule) parameter to PAF, I'll be happy to review it.

/ioguix

[blogh]

I did the pull request.

To answer my question from earlier: postgresql.auto.conf goes into the data_directory when data_directory and PGDATA are different.

Benoit

[ioguix]

I just answered in #34.

So now, it boil down to:

• couldn't this be dealt with the start_opts param ?
• ... or finishing to review you patch, discussing a bit and accept it.

[ioguix]

Replying to myself: we can not use start_opts because the RA itself needs to know about the datadir path for PG_VERSION, recovery.conf, the controldata and so on...

Sorry for the noise :)

[ioguix]

I just merged your PR from #35.

ISTM we can now close this issue.

Thank you!