0

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 5432

My Question: Why does this happen?

My order of operations:

  1. 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
    

then

  1. stop postgresql
  2. make a new directory /mnt/whatever
  3. mount /dev/xvdb to /mnt/whatever/
  4. Copy contents of '/var/lib/postgresql/9.5/main/' into new volume '/mnt/whatever'

  5. 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)
    
  6. Start postgresql

  7. 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).

Any suggestions?

My guess is that its some kind of lsof thing, but I'm stuck.

  • 1
    Looking at the logfile of postgresql could help. – Gerald Schneider Mar 21 at 14:27
0

5) Copy contents of '/var/lib/postgresql/9.5/main/' into new volume '/mnt/whatever'

The most plausible reason why, after doing that, PostgreSQL fails immediately on startup, would be that this step does not keep the exact permissions/owners of files and directories that get copied. If you use cp, make sure to add the -p option.

7) Start postgresql

8) netstat and find that postgresql is no longer listening on 5432

You seem to search for a network problem but not listening on port 5432 is more likely to be due to PostgreSQL exiting immediately in error after step #7. As mentioned in the comments, check the postgresql logs: the reason why it fails to start should be written explicitly there. Typically the logs are in /var/log/postgresql/ (given the paths mentioned it looks like a Debian system).

  • Thank you!! So simple, it was permissions! I created the new directory as "root:root" instead of "postgres:postgres", fixing permissions fixes the issue. Thanks! – P-Didz Mar 22 at 14:10
  • @P-Didz: You're welcome! Please accept the answer if your're satisfied with it. – Daniel Vérité Mar 22 at 15:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.