Обсуждение: Moving pg_xlog folder

Поиск
Список
Период
Сортировка

Moving pg_xlog folder

От
Wei Shan
Дата:
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

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

Re: Moving pg_xlog folder

От
Venkata Balaji N
Дата:
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"?

Is PostgreSQL up and running ? What does "pg_ctl -D <data directory> status" say ?

Or, if you have enabled logging, does postgresql logs say anything ?
 
Regards,
Venkata B N 

Re: Moving pg_xlog folder

От
Raghavendra
Дата:
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

Re: Moving pg_xlog folder

От
Wei Shan
Дата:
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

Re: Moving pg_xlog folder

От
Venkata Balaji N
Дата:

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