Postgresql
Exécuter les commandes en postgres :
su – postgres
Exécuter l’invite de commande :
psql
psql (9.2.14)
Type « help » for help.
Connexion distante :
psql -h hostname -U user -W
Voir les bases de données :
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
———————+———-+———–+———+——-+———————–
postgres | postgres | SQL_ASCII | C | C |
Se connecter à une base de données :
postgres=# \c postgres
You are now connected to database « postgres » as user « postgres ».
Lister la liste des tables :
postgres=# \dt
List of relations
Schema | Name | Type | Owner
——–+—————————————+——-+—————
Lister les utilisateurs :
postgres=# \du
List of roles
Role name | Attributes | Member of
—————+————————————————+———–
postgres | Superuser, Create role, Create DB, Replication | {}
Configuration du fichier postgresql.conf :
shared_buffers = xG
max_connections = 100
listen_addresses = ‘xxx.xxx.xxx.xxx’
Streaming postgres replication :
Master :
initdb
CREATE ROLE rep WITH REPLICATION PASSWORD ‘password’ LOGIN
postgresql.conf
wal_level = replica ou hot_standby
wal_keep_segments = 64
max_wal_senders = 10
ou
max_replication_slots = 10
primary_slot_name = ‘name_of_slave’
pg_hba.conf
# TYPE DATABASE USER ADDRESS METHOD
host replication rep x.x.x.x/32 md5
Slave :
postgresql.conf
hot_standby = on
recovery.conf
standby_mode = ‘on’
primary_conninfo = ‘host=x.x.x.x port=5432 user=rep password=’password’
ou
primary_slot_name = ‘name_of_slave’
recovery_target_timeline = ‘latest’ (cascading replication)
trigger_file = ‘/home/postgres/master’
Initialisation du slave :
pg_basebackup -h x.x.x.x -D /var/lib/postgresql/10/main/ -P -U rep
Voir l’activité de postgres :
select * from pg_stat_activity;
select * from pg_stat_replication;