PostGreSQL

Installation de Postgres

Source : https://www.postgresqltutorial.com/postgresql-getting-started/install-postgresql-linux/

Création d'une base et d'un utilisateur

sudo -u postgres psql
create database mydb;
create user myuser with encrypted password 'mypass';
grant all privileges on database mydb to myuser;

Autoriser l'accès distant à un serveur Postgres

1. Editer le fichier /etc/postgresql/postgresql.conf
2. Modifier la ligne suivante :

#listen_addresses = 'localhost'         # what IP address(es) to listen on;
                                       # comma-separated list of addresses;
                                       # defaults to 'localhost'; use '*' for all
                                       # (change requires restart)     
listen_addresses = '*'

1. Editer le fichier /etc/postgresql/pg_hba.conf
2. Modifier la ligne suivante :

host    all             all             0.0.0.0/24              trust

Modifier le mot de passe postgres

ALTER USER postgres WITH PASSWORD 'new_password';

Changer l'encodage d'un template

First, we need to drop template1. Templates can’t be dropped, so we first modify it so t’s an ordinary database:

UPDATE pg_database SET datistemplate = FALSE WHERE datname = 'template1';

Now we can drop it:

DROP DATABASE template1;

Now its time to create database from template0, with a new default encoding:

CREATE DATABASE template1 WITH TEMPLATE = template0 ENCODING = 'UNICODE';

Now modify template1 so it’s actually a template:

UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'template1';

Now switch to template1 and VACUUM FREEZE the template:

\c template1
VACUUM FREEZE;

Problem should be resolved.