Postgresql – How to change the config_file location of a postgresql database for “service postgresql start”

databasepostgresqlservice

I had a PostgreSQL 9.1 db that I just upgraded to 9.3 using

/usr/lib/postgresql/9.3/bin/pg_upgrade -b /usr/lib/postgresql/9.1/bin -B /usr/lib/postgresql/9.3/bin -d /var/lib/postgresql/9.1/main/ -D /var/lib/postgresql/9.3/main/ -O "-c config_file=/etc/postgresql/9.3/main/postgresql.conf" -o "-c config_file=/etc/postgresql/9.1/main/postgresql.conf"

following the instructions here, unfortunately realizing after that I hadn't looked to closely at the last bit.

After the upgrade (which was successful), I found that instead of the default location of the data directory (in this case /var/lib/postgresql/9.3/main), the option in the upgrade had now configured all of my PostgreSQL config files to be located at /etc/postgresql/9.1/main/.

I've spent a while looking for a way to change this and so far I've found that while hba_file and ident_file can be easily changed in postgresql.conf itself, the config_file cannot be set except through the postgres command line interface.

I tried using postgres with the -o "-c config_file=/var/lib/postgresql/9.3/main" option but it seems that this just tries to start another instance of the service, separate from what a service postgresql start does.

Running pg_ctl status -D ~/9.3/main/ I get

`/usr/lib/postgresql/9.3/bin/postgres "-D" "/var/lib/postgresql/9.3/main" "-c" "config_file=/etc/postgresql/9.3/main/postgresql.conf"`

which I take to mean that -c... /etc... is being passed to whatever starts the service. Based on my limited knowledge, I believe this to be /etc/init.d/postgresql with the start parameter. Looking into that, it seems to reference /usr/share/postgresql-common/init.d-functions which in turn contains the following towards the top

do_ctl_all() {
[ "$1" ] || { echo "Error: invalid command '$1'" >&2; exit 1; }
[ "$2" ] || { echo "Error: invalid version '$2'" >&2; exit 1; }
[ -d "/etc/postgresql/$2" ] || return 0
[ "$(ls /etc/postgresql/$2)" ] || return 0
[ -x "/usr/lib/postgresql/$2/bin/postmaster" ] || return 0
...

though I'm not sure if these have any relevance.

What I guess I'm asking is if there is any way to configure the config_file variable or customize how the service starts.

Thanks for any help! Sorry for my lack of understanding, I'm trying to learn more!

Best Answer

Copy all your configuration files to /etc/postgresql/9.3/main/. Make any adjustments that you want to make as part of the upgrade. Then you should be able to start the new server using

sudo pg_ctlcluster 9.3 main start

As you have figured out, the Debian packaging of PostgreSQL requires postgresql.conf to be located in that place, because it starts there to figure out where everything else is located, including the data directory and the other configuration files. Make sure your postgresql.conf points to the right places for those.

Next time, use pg_upgradecluster -m upgrade 9.1 main, and it will figure all of this out for you automatically.