Skip to content

postgres

Postgres relates work instructions.

At current time (jan 2021) the postgres version used is the latest (13) and this part describes how to setup the server. Note that the code has functions like ensure_schema() and ensure_database() that take care of creating the tables.

installing postgres

remove old version

I installed postgres 13 over postgres 12 because of some deprecation warnings so this also takes care of removing an older postgres. Note that this wipes all databases as well !!

remove and purge
1
2
3
4
5
6
apt-get --purge remove postgresql*
rm -r /etc/postgresql/
rm -r /etc/postgresql-common/
rm -r /var/lib/postgresql/
userdel -r postgres
groupdel postgres

install latest

Now make sure you have the repository for the latest postgres installed :

install
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
# Create the file repository configuration:
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

# Import the repository signing key:
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

# Update the package lists:
sudo apt-get update

# Install the latest version of PostgreSQL.
# If you want a specific version, use 'postgresql-12' or similar instead of 'postgresql':
sudo apt-get -y install postgresql

In this case it was postgres 13 that was installed. Maybe at a later time it will be 14.

configure

Especially pg_hba.conf should be altered so we can log in from the backend. This would do the trick :

pg_hba.conf
1
2
3
4
5
6
echo "local all postgres peer" > /etc/postgresql/13/main/pg_hba.conf
echo "local all all md5" >> /etc/postgresql/13/main/pg_hba.conf
echo "host all all 127.0.0.1/32 md5" >> /etc/postgresql/13/main/pg_hba.conf
echo "host all all .. code-block::1/128 md5" >> /etc/postgresql/13/main/pg_hba.conf
# or... edit the file and alter this manually
/vim /etc/postgresql/13/main/pg_hba.conf

If you edit it you mainly only have to alter the line :

pg_hba.conf
1
2
3
local   all             all                                     peer
# into 
local   all             all                                     md5

The main test to see if it works

test it
1
2
3
4
5
6
7
psql -U klopt
psql: error: FATAL:  Peer authentication failed for user "klopt
vi /etc/postgresql/13/main/pg_hba.conf  # alter
systemctl restart postgresql
# now the error changes (even if user klopt does not exist yet)
psql -U klopt
Password for user klopt:

Now you should add the user to run the server as :

create user
1
2
3
4
sudo su -c "createuser klopt" postgres
sudo su -c "createdb klopt" postgres
sudo su -c 'psql -d klopt -c "create extension "uuid-ossp""' postgres
sudo su -c 'psql -d klopt -c "CREATE ROLE klopt LOGIN ENCRYPTED PASSWORD 'yousecret'"' postgres

And now you still can't access the tables until :

grant privileges
1
2
GRANT ALL PRIVILEGES ON DATABASE klopt TO klopt;
GRANT ALL PRIVILEGES ON all TABLES IN SCHEMA public TO klopt;

If you get this error when accessing a table :

troubleshooting
1
2
klopt=> INSERT INTO locations (ZIP,NUM,STREET,CITY,X,Y) VALUES ('3151AW','248','Prins Hendrikstraat','Hoek van Holland','5.68182','50.7547') ON CONFLICT (zip,num,annex) DO UPDATE  SET ZIP='3151AW',NUM='248',STREET='Prins Hendrikstraat',CITY='Hoek van Holland',X='5.68182',Y='50.7547' RETURNING id;
ERROR:  permission denied for sequence locations_id_seq

Most probably some schema or table is still owned by postgres :

view ownership
1
2
3
4
5
6
7
8
klopt=> \dn
  List of schemas
      Name      |  Owner   
----------------+----------
 kees@klopt.org | klopt
 public         | postgres
 test@klopt.org | klopt
 (3 rows)

You will need to alter all schemas and tables to write to them as 'klopt' :

change ownership
1
2
3
4
5
6
7
alter schema public owner to klopt;
ALTER SCHEMA
alter table public.locations owner to klopt;
ALTER TABLE
klopt=# alter table public.orders owner to klopt;
ALTER TABLE
# all of them .....

connection error

If you get this error, psql is listing on localhost but not at 192.168.1.151

psql -h 192.168.1.151 -U klopt
psql: error: could not connect to server: Connection refused
        Is the server running on host "192.168.1.151" and accepting
        TCP/IP connections on port 5432?

Alter postgres.conf to

/etc/postgres/13/main/postgres.conf
listen_addresses = '*'

And restart :

systemctl restart postgresql