PostgreSQL configuration

From Smith family
Jump to: navigation, search
Server setup
← Previous Next →
MySQL config Web server

If Posgtres isn't already installed, install it:

root@server:~# aptitude install postgresql postgresql-client

On the desktop, install pgadmin3 as well:

root@desktop:~# aptitude install postgresql postgresql-client pgadmin3

Passwords

  • Connect to the Postres server:
root@server:~# sudo -u postgres psql postgres
  • Set a password for the "postgres" database role using the command:
postgres=# \password postgres
Ctrl-D to exit

Remote access

Edit /etc/postgresql/9.1/main/pg_hba.conf and modify the default configuration which is very protective. For example, if you want postgres to manage its own users (not linked with system users), you will add the following lines:

# TYPE  DATABASE    USER        IP-ADDRESS        IP-MASK           METHOD
# Allow connections from across the LAN
host    all         all         192.168.1.0       255.255.255.0     md5
# Allow any connection from this machine
local   all         all                                             md5
host    all         all         127.0.0.1/32                        md5

Edit /etc/postgresql/9.1/main/postgresql.conf to include

listen_addresses = '192.168.1.251,localhost'

Note that the firewall is configured to only allow connections to PostgreSQL from machines on the LAN.

Updating

It's perfectly possible to have several instances of PostreSQL running at different versions, such as 9.5 and 10.1.

To migrate the data and remove the old version, follow these steps:

  • Use `pg_lsclusters` to see what's running.
root@desktop:~# pg_lsclusters
Ver Cluster Port Status Owner    Data directory               Log file
9.5 main    5432 online postgres /var/lib/postgresql/9.5/main /var/log/postgresql/postgresql-9.5-main.log
10  main    5433 online postgres /var/lib/postgresql/10/main  /var/log/postgresql/postgresql-10-main.log
  • Check what's in what cluster
root@server::# su - postgres 
postgres@server:~$ psql --cluster 9.5/main 
postgres=# select datname from pg_database;
  datname  
-----------
 template1
 template0
 postgres
 ...
 ...
(5 rows)
postgres=# \q
(where `...` indicates some more databases in the cluster)
postgres@server:~$ psql --cluster 10/main  
psql (10.5 (Ubuntu 10.5-0ubuntu0.18.04))
postgres=# select datname from pg_database;
  datname  
-----------
 postgres
 template1
 template0
(3 rows)
this indicates that there's nothing currently in the version 10 cluster.
  • Stop Posgres
root@server:~# systemctl stop postgresql  
  • Destroy the existing empty cluster in the version 10 cluster
root@server:~# pg_dropcluster --stop 10 main
  • Move the existing data to version 10
root@server:~# pg_upgradecluster -m upgrade 9.5 main 
  • Drop the 9.5 cluster
root@server:~# pg_dropcluster --stop 9.5 main
  • Use `aptitude` to purge all the postgresql-9.5 packages.
  • Restart Postgres
root@server:~# systemctl start postgresql  

See also

Here are a few pages that are useful guides or provide background and context.