Postgres Pro
Downloads
Home   >   mailing lists

Re: Moving pg_xlog folder - Mailing list pgsql-admin

From Venkata Balaji N
Subject Re: Moving pg_xlog folder
Date
Msg-id CAEyp7J-V1iKOaw+pxp4wWurPJuQsW2=bBw+9-=6yeTxTdkra8A@mail.gmail.com
Whole thread Raw
In response to Re: Moving pg_xlog folder  (Wei Shan <weishan.ang@gmail.com>)
List pgsql-admin
Tree view

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.

Or, you can say "psql -p 5433" - that can help you connect successfully.

Good to know that everything is working fine. When you run "psql" without any options, it would by default take the port as 5432 or whatever port you have assigned to PGPORT environment variable. If you want to connect to PostgreSQL with a different port and PGPORT is not configured, then you need to exclusively mention the port with -p option.

Regards,
Venkata B N

Fujitsu Australia



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: Wei Shan
Date:
Subject: Re: Moving pg_xlog folder
Next
From: Dave Johansen
Date:
Subject: Re: database must be vacuumed with transactions