Postgres Pro
Downloads
Home   >   mailing lists

Re: Moving pg_xlog folder - Mailing list pgsql-admin

From Raghavendra
Subject Re: Moving pg_xlog folder
Date
Msg-id CA+h6AhjCpQG8ydoCMgYX=1PHNut-RNbTt=r_K0O_Ktz7BFm7Sg@mail.gmail.com
Whole thread Raw
In response to Moving pg_xlog folder  (Wei Shan <weishan.ang@gmail.com>)
Responses Re: Moving pg_xlog folder
List pgsql-admin
Tree view
On Sun, Jun 28, 2015 at 12:19 AM, Wei Shan <weishan.ang@gmail.com> wrote:
Hi all,

OS: CentOS
PG version: 9.3

I'm testing the scenario of moving the xlog folder to a fast pair of disk. This was the steps I followed;

1. create new mountpoint with the correct permissions
2. Shutdown DB
3. Create softlink from new mountpoint to $PG_DATA/pg_xlog
4. Startup DB


​If I understand here correctly, you want to move $PGDATA/pg_xlog to "/fast/disk/pg_xlog" directory. 

If I want to implement the same on my local machine of moving of pg_xlog to other directory (in your case other mount point). I will follow below steps:

1) Stop the running cluster

-bash-4.2$ pwd
/opt/PostgreSQL/9.4
/opt/PostgreSQL/9.4/bin/pg_ctl -D /opt/PostgreSQL/9.4/data/ stop -mf

2) Create a new directory (In your case new mount point with correct permission)

mkdir temp_pgxlog

3) Copy existing files and directory from current $PGDATA/pg_xlog to new mount point directory.

cp -rf /opt/PostgreSQL/9.4/data/pg_xlog/*   /opt/PostgreSQL/9.4/temp_pgxlog/

4) Create symbolic link to new directory by renaming existing directory.

cd /opt/PostgreSQL/9.4/data/
mv pg_xlog/ pg_xlog_bkp
ln -s ~/temp_pgxlog/ pg_xlog

5) Start the cluster.

/opt/PostgreSQL/9.4/bin/pg_ctl -D /opt/PostgreSQL/9.4/data/ start
 

However, after I start the DB, I hit the following error;

-bash-4.1$ psql

psql: could not connect to server: No such file or directory

Is the server running locally and accepting

connections on Unix domain socket "/tmp/.s.PGSQL.5432"?


This issue resolves by itself once I reboot the server after I move the XLOG. Any ideas what I did wrongly?

Thanks!
--
Regards,
Ang Wei Shan

​Reboot of the server not required, have you check below areas before rebooting.

1) Is symbolic link of $PGDATA/pg_xlog pointing to new location  ?

2) What information you see in the Database server logs ?

​---
Regards,
Raghavendra
EnterpriseDB Corporation

pgsql-admin by date:

Previous
From: Cliff Pratt
Date:
Subject: Re: SELECT column WHERE time_type = 'Break' but only the rows that are after(below) time_type = 'Start'
Next
From: Wei Shan
Date:
Subject: Re: Moving pg_xlog folder