16

I use postgresql on Debian. The postgresql service can not start after I edit the config file:

#data_directory = '/var/lib/postgresql/9.4/main'                # use data in another directory
data_directory = '/opt/data/postgresql/data'

(yeah,I just use custom directory instead of the default data_directory)

I find the log in /var/log/syslog

Sep 14 10:22:17 thinkserver-ckd postgresql@9.4-main[11324]: Error: could not exec /usr/lib/postgresql/9.4/bin/pg_ctl /usr/lib/postgresql/9.4/bin/pg_ctl start -D /opt/data/postgresql/data -l /var/log/postgresql/postgresql-9.4-main.log -s -o  -c config_file="/etc/postgresql/9.4/main/postgresql.conf" :
Sep 14 10:22:17 thinkserver-ckd systemd[1]: postgresql@9.4-main.service: control process exited, code=exited status=1
Sep 14 10:22:17 thinkserver-ckd systemd[1]: Failed to start PostgreSQL Cluster 9.4-main.
Sep 14 10:22:17 thinkserver-ckd systemd[1]: Unit postgresql@9.4-main.service entered failed state.

And nothing in /var/log/postgresql/postgresql-9.4-main.log

Thanks.

I finally got this answer:

What this error means in PostgreSQL?

@langton 's answer.

He said that

you should run pg_upgradecluster or similar, or just create a new cluster with pg_createcluster (these commands are for debian systems - you didn't specify your OS)

So I executed the command:

pg_createcluster -d /opt/data/postgresql/data -l /opt/data/postgresql/log 9.4 ckd

And then : service postgresql restart

it started!

Community
  • 1
  • 1
Billy Yang
  • 357
  • 4
  • 14
  • check the directory to make sure that postgres has read/write/execute rights on the directory and its contents. Im not sure if Debian uses SELINUX, but if it does, then make sure that the folder has the right context so that postgres can access it. (one way to quickly check if SELINUX is involved is to temporarily set it to permissive and see if that makes the problem go away) – PabTorre Sep 14 '15 at 04:26
  • 1
    @PabTorre I have checked the permission of data directory,and I am sure it is not the problem. The reason was just I need a init of the db. Thanks all the same – Billy Yang Sep 14 '15 at 05:49
  • why not specify this directory on install ? – VynlJunkie May 23 '18 at 11:52

1 Answers1

0

If downtime is allowed and you already have databases with data in the old cluster location you only need to physically copy the data to the new location.

This is a more or less common operation if you partition is out of space.

# Check that current data directory is the same that
# the one in the postgresql.conf config file
OLD_DATA_DIR=$(sudo -u postgres psql --no-psqlrc --no-align --tuples-only --quiet -c "SHOW data_directory;")
echo "${OLD_DATA_DIR}"

CONFIG_FILE=$(sudo -u postgres psql --no-psqlrc --no-align --tuples-only --quiet -c "SHOW config_file;")
echo "${CONFIG_FILE}"

# Stop PostgreSLQ
systemctl stop postgresql

# Change the data directory in the config
# Better to do it with an editor, instead of sed
NEW_DATA_DIR='/opt/data/postgresql/data'
sed -i "s%data_directory = '${OLD_DATA_DIR}'%data_directory = '${NEW_DATA_DIR}'%" "${CONFIG_FILE}"

# Move/Copy the data for example using rsync
rsync -av --dry-run "${OLD_DATA_DIR}" "${NEW_DATA_DIR}"

# Take care with the classical issues of rsync and end backslashes
rsync -av "${OLD_DATA_DIR}" "${NEW_DATA_DIR}"

# Rename the old dir, just to avoid missunderstandings and set
# check the permissions on the new one

# Start postgres
systemctl  start postgresql

# Check that everything goes well and eventually drop the old data
# Make sure that the logs and everything else is where you want.
Francisco Puga
  • 23,869
  • 5
  • 48
  • 64