CODEWITHSUNDEEP
postgresqlstartupdatadirectory
Billy Yang
Billy Yang

Reputation: 357

postgresql can not start after change the data_directory

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 [email protected][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]: [email protected]: 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 [email protected] 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!

Upvotes: 16

Views: 7674

Answers (1)

Francisco Puga
Francisco Puga

Reputation: 25158

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.

Upvotes: 1

Related Questions