Postgres Pro
Downloads
Home   >   mailing lists

Re: Moving pg_xlog folder - Mailing list pgsql-admin

From Wei Shan
Subject Re: Moving pg_xlog folder
Date
Msg-id CAFe9ZTrz5V0PRrBNXMm9jUfveST7OoOMt70VRysiiVA7ejcBVQ@mail.gmail.com
Whole thread Raw
In response to Re: Moving pg_xlog folder  (Raghavendra <raghavendra.rao@enterprisedb.com>)
Responses Re: Moving pg_xlog folder
List pgsql-admin
Tree view
Hi all,

Output from pg_ctl -D $PGDATA status;

-bash-4.1$ pg_ctl -D /var/lib/pgsql/9.3/data status

pg_ctl: server is running (PID: 1983)

/usr/pgsql-9.3/bin/postgres

Output from logfile;

< 2015-04-16 04:28:15.773 HKT >LOG:  database system was shut down at 2015-04-16 04:15:55 HKT

< 2015-04-16 04:28:15.776 HKT >LOG:  database system is ready to accept connections

< 2015-04-16 04:28:15.777 HKT >LOG:  autovacuum launcher started

I found out something weird though;

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"?

-bash-4.1$ cd /tmp

-rw-------.  1 postgres postgres   50 Apr 16 04:28 .s.PGSQL.5433.lock

srwxrwxrwx.  1 postgres postgres    0 Apr 16 04:28 .s.PGSQL.5433

However, after I reboot the system, even though in postgresql, it's configured to listen at 5433, in the /tmp folder; 

-rw-------.  1 postgres postgres   50 Apr 16 04:38 .s.PGSQL.5432.lock

srwxrwxrwx.  1 postgres postgres    0 Apr 16 04:38 .s.PGSQL.5432

postgres=# show port;

 port 

------

 5432

(1 row)


It seems that even when the port was configured in postgresql.conf to be 5433, it was still running in 5432. After I reconfigured the port to be 5432, the steps are working fine already.

Thanks!



On 28 June 2015 at 14:43, Raghavendra <raghavendra.rao@enterprisedb.com> wrote:
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




--
Regards,
Ang Wei Shan

pgsql-admin by date:

Previous
From: Raghavendra
Date:
Subject: Re: Moving pg_xlog folder
Next
From: Venkata Balaji N
Date:
Subject: Re: Moving pg_xlog folder