Re: Moving pg_xlog folder

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(dot)rao(at)enterprisedb(dot)com>
wrote:

> On Sun, Jun 28, 2015 at 12:19 AM, Wei Shan <weishan(dot)ang(at)gmail(dot)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
> Blog: http://raghavt.blogspot.com/
>
>

--
Regards,
Ang Wei Shan