I have a temporary need for a 180GB postgresql database. For costs sake, I have this on a cheap 40GB disk server and I want to mount a 200GB volume to the postgresql data directory.
My problem: After I stop postgresql, mount the volume, copy contents, update postgresql.conf, start postgresql, it stops listening on
My Question: Why does this happen?
My order of operations:
netstat and see that postgres is listening on 5432 for any remote connections
root@foobar:/etc/postgresql/9.5/main# netstat -tulpen Active Internet connections (only servers) Proto Recv-Q Send-Q Local Address Foreign Address State User Inode PID/Program name tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN 112 44384 25831/postgres tcp 0 0 0.0.0.0:3451 0.0.0.0:* LISTEN 0 94839 15010/sshd tcp6 0 0 :::5432 :::* LISTEN 112 44385 25831/postgres tcp6 0 0 :::3451 :::* LISTEN 0 94848 15010/sshd
- stop postgresql
- make a new directory /mnt/whatever
- mount /dev/xvdb to /mnt/whatever/
Copy contents of '/var/lib/postgresql/9.5/main/' into new volume '/mnt/whatever'
Edit postgresql.conf and change "data directory" to new volume..
data_directory = '/mnt/whatever' # use data in another directory # (change requires restart) hba_file = '/etc/postgresql/9.5/main/pg_hba.conf' # host-based authentication file # (change requires restart) ident_file = '/etc/postgresql/9.5/main/pg_ident.conf' # ident configuration file # (change requires restart)
netstat and find that postgresql is no longer listening on 5432
root@foobar:/# netstat -tulpen Active Internet connections (only servers) Proto Recv-Q Send-Q Local Address Foreign Address State User Inode PID/Program name tcp 0 0 0.0.0.0:3451 0.0.0.0:* LISTEN 0 94839 15010/sshd tcp6 0 0 :::3451 :::* LISTEN 0 94848 15010/sshd
At first I thought that it was something in the
postgresql.conf that I messed up allowing remote connections, but after rebuilding the server several times going through this process, it definitely listens on
5432 all the way up until I mount the new volume.
Postgresql works fine remotely until I mount a volume and update the data dir in the conf. Also, it won't start listening even after I unmount the volume and restore a backup of the original
postgresql.conf. It seems to me that something I'm doing in the mount is breaking it.
I have made a rule allowing
5432 in IPtables, but that doesn't fix it (and remember, remote connections work fine before the mount, so I don't think its firewall).
My guess is that its some kind of
lsof thing, but I'm stuck.