Allow Remote Connections

Problem

Remote connections are needed for troubleshooting and also creating reports or you would probably just like to connection your postgres client to the database.

Solution

  • Check the ports that postgres is listening on:

vagrant@ubuntu-xenial:~$ sudo netstat -tupln
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 1104/sshd
tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN 1336/postgres

  • Then edit the PostgreSQL configuration file and put the IP that you would like to allow. If you want to allow all IPs, then use 0.0.0.0

$ sudo vim /etc/postgresql/9.5/main/pg_hba.conf

  • Then add the following line to end of the file and then save and quit.

host   all     all     0.0.0.0/0       md5

  • Next Allow TCP/IP socket. Open the config file below and change listen directive from localhost to * for allow all.

$ sudo vim /etc/postgresql/9.5/main/postgresql.conf

  • Search for the line, listen_addresses. Un-comment the line and then change localhost to * (you can use comma-separated list of addresses; * is all ip address)

#listen_addresses = 'localhost'         # what IP address(es) to listen on;

listen_addresses = '*'          # what IP address(es) to listen on;

  • Restart postgres.

vagrant@ubuntu-xenial:~$ ps aux | grep postgres
postgres 1336 0.0 0.7 293436 24224 ? S Apr24 0:03 /usr/lib/postgresql/9.5/bin/postgres -D /var/lib/postgresql/9.5/main -c config_file=/etc/postgresql/9.5/main/postgresql.conf
postgres 1359 0.0 1.1 293568 35892 ? Ss Apr24 0:01 postgres: checkpointer process
postgres 1360 0.0 0.1 293436 5596 ? Ss Apr24 0:01 postgres: writer process
postgres 1361 0.0 0.2 293436 8756 ? Ss Apr24 0:01 postgres: wal writer process
postgres 1362 0.0 0.2 293872 6532 ? Ss Apr24 0:01 postgres: autovacuum launcher process
postgres 1363 0.0 0.1 148948 4132 ? Ss Apr24 0:02 postgres: stats collector process
vagrant@ubuntu-xenial:~$ sudo service postgresql stop
vagrant@ubuntu-xenial:~$ ps aux | grep postgres
vagrant 18972 0.0 0.0 12944 936 pts/0 S+ 11:30 0:00 grep --color=auto postgres
vagrant@ubuntu-xenial:~$ sudo service postgresql start
vagrant@ubuntu-xenial:~$ ps aux | grep postgres
postgres 19010 0.2 0.7 293436 24344 ? S 11:30 0:00 /usr/lib/postgresql/9.5/bin/postgres -D /var/lib/postgresql/9.5/main -c config_file=/etc/postgresql/9.5/main/postgresql.conf
postgres 19012 0.0 0.1 293436 3980 ? Ss 11:30 0:00 postgres: checkpointer process
postgres 19013 0.0 0.1 293436 3980 ? Ss 11:30 0:00 postgres: writer process
postgres 19014 0.0 0.1 293436 3980 ? Ss 11:30 0:00 postgres: wal writer process
postgres 19015 0.0 0.2 293876 6740 ? Ss 11:30 0:00 postgres: autovacuum launcher process
postgres 19016 0.0 0.1 148948 4100 ? Ss 11:30 0:00 postgres: stats collector process
vagrant 19032 0.0 0.0 12944 1020 pts/0 S+ 11:30 0:00 grep --color=auto postgres

 

  • Then check if postgres is listening to all All connections

vagrant@ubuntu-xenial:~$ sudo netstat -tupln
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 1104/sshd
tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN 19010/postgres
tcp6 0 0 :::443 :::* LISTEN 1350/apache2
tcp6 0 0 :::80 :::* LISTEN 1350/apache2
tcp6 0 0 :::8083 :::* LISTEN 1350/apache2
tcp6 0 0 :::22 :::* LISTEN 1104/sshd
tcp6 0 0 :::5432 :::* LISTEN 19010/postgres
udp 0 0 0.0.0.0:68 0.0.0.0:* 856/dhclient

References

How Do I Enable remote access to PostgreSQL database server?

Speed insert or update query running on a large postgres table

Got this tip from the this post:

Problem

When you run a large query (insert/update) on a huge table with several indexes, these indexes can seriously slow the query execution.

Solution

With Postgresql it can be faster to disable the indexes before running the query and reindex all the table afterwards.

You can do it like this :

  1. Disable all table indexes
UPDATE pg_index
SET indisready=false
WHERE indrelid = (
    SELECT oid
    FROM pg_class
    WHERE relname='<TABLE_NAME>'
);
  1. Run your query
UPDATE <TABLE_NAME> SET ...;
  1. Re-enable all table indexes
UPDATE pg_index
SET indisready=true
WHERE indrelid = (
    SELECT oid
    FROM pg_class
    WHERE relname='<TABLE_NAME>'
);
  1. Re-index table