Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm trying to mount the WAL to a different disk. I stopped the server, and did the following:

(I'm using Fabric)

  # Move pg_xlog to wals/path.dev/
  dest_dir = "/home/ec2-user/%s/wals/%s" % (config['main_dir'], dev.replace('/','.'))
  src_dir = "/var/lib/pgsql/%s/data/pg_xlog" % version

  # Move all files 
  sudo("mkdir %s" % (dest_dir), warn_only = True)

  # Mount .fab-pg/wals/path.to.dev/
  mount(dev, dest_dir, fs)

  # Point it at new origin
  sudo("sudo mv %s* %s" % (src_dir, dest_dir))
  dest_dir = "%s/pg_xlog" % dest_dir # pg_xlog was moved into the directory
  sudo("ln -s %s %s" % (dest_dir, src_dir))

  # Grant postgres permissions
  sudo("chown postgres %s" % dest_dir)
  sudo("chown postgres %s" % src_dir)

PG Log

LOG:  received smart shutdown request
LOG:  autovacuum launcher shutting down
LOG:  shutting down
LOG:  database system is shut down
LOG:  database system was shut down at 2012-12-13 03:00:46 UTC
FATAL:  required WAL directory "pg_xlog" does not exist
LOG:  startup process (PID 30971) exited with exit code 1
LOG:  aborting startup due to startup process failure

Ls

[ec2-user@ip-10-205-21-74 fab-postgres]$ sudo ls -al /var/lib/pgsql/9.2/data
total 96
drwx------ 14 postgres postgres  4096 Dec 13 03:02 .
drwx------  4 postgres postgres  4096 Dec 12 04:03 ..
drwx------  5 postgres postgres  4096 Dec 12 03:57 base
drwx------  2 postgres postgres  4096 Dec 13 03:00 global
drwx------  2 postgres postgres  4096 Dec 12 03:57 pg_clog
-rw-------  1 postgres postgres  4476 Dec 12 03:57 pg_hba.conf
-rw-------  1 postgres postgres  1636 Dec 12 03:57 pg_ident.conf
drwx------  2 postgres postgres  4096 Dec 13 03:02 pg_log
drwx------  4 postgres postgres  4096 Dec 12 03:57 pg_multixact
drwx------  2 postgres postgres  4096 Dec 13 03:02 pg_notify
drwx------  2 postgres postgres  4096 Dec 12 03:57 pg_serial
drwx------  2 postgres postgres  4096 Dec 12 03:57 pg_snapshots
drwx------  2 postgres postgres  4096 Dec 13 03:00 pg_stat_tmp
drwx------  2 postgres postgres  4096 Dec 12 03:57 pg_subtrans
drwx------  2 postgres postgres  4096 Dec 12 03:57 pg_tblspc
drwx------  2 postgres postgres  4096 Dec 12 04:03 pg_twophase
-rw-------  1 postgres postgres     4 Dec 12 03:57 PG_VERSION
lrwxrwxrwx  1 root     root        30 Dec 13 03:01 pg_xlog -> .fab-pg/wals/.dev.xvdf/pg_xlog
-rw-------  1 postgres postgres 19587 Dec 12 03:57 postgresql.conf
-rw-------  1 postgres postgres    59 Dec 13 03:02 postmaster.opts

ls

[ec2-user@ip-10-205-21-74 fab-postgres]$ sudo ls -al ~/.fab-pg/wals/.dev.xvdf/
total 12
drwxr-xr-x 3 root     root     4096 Dec 13 03:01 .
drwxrwxr-x 3 ec2-user ec2-user 4096 Dec 13 03:01 ..
drwx------ 3 postgres postgres 4096 Dec 12 04:03 pg_xlog

fixed pg_xlog to point to correct place

[ec2-user@ip-10-205-21-74 fab-postgres]$ sudo ls -al /var/lib/pgsql/9.2/data
total 96
drwx------ 14 postgres postgres  4096 Dec 13 14:42 .
drwx------  4 postgres postgres  4096 Dec 13 14:38 ..
drwx------  5 postgres postgres  4096 Dec 13 14:38 base
drwx------  2 postgres postgres  4096 Dec 13 14:38 global
drwx------  2 postgres postgres  4096 Dec 13 14:38 pg_clog
-rw-------  1 postgres postgres  4476 Dec 13 14:38 pg_hba.conf
-rw-------  1 postgres postgres  1636 Dec 13 14:38 pg_ident.conf
drwx------  2 postgres postgres  4096 Dec 13 14:42 pg_log
drwx------  4 postgres postgres  4096 Dec 13 14:38 pg_multixact
drwx------  2 postgres postgres  4096 Dec 13 14:42 pg_notify
drwx------  2 postgres postgres  4096 Dec 13 14:38 pg_serial
drwx------  2 postgres postgres  4096 Dec 13 14:38 pg_snapshots
drwx------  2 postgres postgres  4096 Dec 13 14:38 pg_stat_tmp
drwx------  2 postgres postgres  4096 Dec 13 14:38 pg_subtrans
drwx------  2 postgres postgres  4096 Dec 13 14:38 pg_tblspc
drwx------  2 postgres postgres  4096 Dec 13 14:38 pg_twophase
-rw-------  1 postgres postgres     4 Dec 13 14:38 PG_VERSION
lrwxrwxrwx  1 root     root        45 Dec 13 14:40 pg_xlog -> /home/ec2-user/.fab-pg/wals/.dev.xvdf/pg_xlog
-rw-------  1 postgres postgres 19587 Dec 13 14:38 postgresql.conf
-rw-------  1 postgres postgres    59 Dec 13 14:42 postmaster.opts
share

migration rejected from stackoverflow.com Jul 2 '13 at 22:35

This question came from our site for professional and enthusiast programmers. Votes, comments, and answers are locked due to the question being closed here, but it may be eligible for editing and reopening on the site where it originated.

closed as off-topic by dezso, Mark Storey-Smith, Nick Chammas, StanleyJohns, swasheck Jul 2 '13 at 22:35

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – dezso, Mark Storey-Smith, Nick Chammas, StanleyJohns, swasheck
If this question can be reworded to fit the rules in the help center, please edit the question.

1  
Could you show the output of ls -l $PGDATA and the exact messages from the server log? –  Peter Eisentraut Dec 12 '12 at 15:57
2  
Does the postgres user have permission to read and list the ~/disk directory? Are you on Fedora/RHEL or another SELinux-enabled distro, where you might need to adjust SELinux policy to allow PostgreSQL to read out of /home ? Always show the exact text of any error message. Always. –  Craig Ringer Dec 12 '12 at 23:54
    
Hey guys, sorry I posted the relevant info. Yes I'm on Amazon's linux AMI which is a RHEL system I believe. There's a policy I need to adjust for PostgreSQL to read out of home? –  joslinm Dec 13 '12 at 3:11
1  
if you do sudo su - and then cd /var/lib/pgsql/9.2/data, what does "ls -l .fab-pg/wals/.dev.xvdf/pg_xlog" give you? I think this is a simple case of your mv and ln -s resulting in a bad link. –  Scott Marlowe Dec 13 '12 at 11:42
    
You're right that was wrong, and I changed it so that it's prefixed with the home directory. I'll update question. I still get the same error at server startup. –  joslinm Dec 13 '12 at 14:43

2 Answers 2

First some troubleshooting.

As root try this:

cd /var/lib/pgsql/9.2/data
cd pg_xlog
ls

See what results you get. If you get errors, troubleshoot that until it works. Then double check your file permissions, and then:

su postgres
cd /var/lib/pgsql/9.2/data
cd pg_xlog
ls

Troubleshoot any errors you get there.

share

The problem is file permissions on the folder and possibly the symlink

chown -R postgres:postgres /home/ec2-user/.fab-pg/wals/.dev.xvdf/pg_xlog
chown -R /var/lib/pgsql/9.2/data/pg_xlog

When working with mysql, I do the same thing. I move data folders and use symlinks with

chown -R mysql:mysql ...

Give it a Try !!!

share