Show replication status in PostgreSQL

https://www.niwi.nz/2013/02/16/replication-status-in-postgresql/

 

select client_addr, state, sent_location, write_location,
        flush_location, replay_location from pg_stat_replication;

This query can output can be like this:

postgres=# select client_addr, state, sent_location,write_location, flush_location, replay_location, sync_priority from pg_stat_replication;

Disable Streaming Replication without restarting either master or slave

I understand the way things work currently, but would it not be more
convenient to let the replication be stopped and started from SQL commands
like:

On Master:

select pg_pause_streaming_replication(slave_fqdn);
select pg_resume_streaming_replication(slave_fqdn);

On Slave:

select pg_pause_streaming_replication();
select pg_pause_streaming_replication();

If Cascaded Streaming Replication is confugured, then

On Slave:

select pg_start_streaming_replication(cascaded_slave_fqdn);
select pg_stop_streaming_replication(cascaded_slave_fqdn);

Or an OS executable [ either in the core or a contrib module] like

pg_replication -p primary_fqdn -s slave_fqdn -a pause
pg_replication -p primary_fqdn -s slave_fqdn -a resume
pg_replication -p primary_fqdn -s slave_fqdn -a setup

[ the last one would awesome; and i do not think that it is impossible, and
would be loved by one and all ]

[script] Backup

backup.txt

Lancement de pgAdmin4

(pgadmin4) titi@serveur:~/home-global/virtualenv/myVE/lib/python2.7/site-packages/pgadmin4/pgadmin4/lib/python2.7$ 
cd /home/titi/home-global/virtualenv/myVE/lib/python2.7/site-packages/pgadmin4/


(pgadmin4) titi@serveur:~/home-global/virtualenv/myVE/lib/python2.7/site-packages/pgadmin4$ python pgAdmin4.py
pgAdmin 4 - Application Initialisation
======================================

The configuration database - '/home/titi/.pgadmin/pgadmin4.db' does not exist.
Entering initial setup mode...
NOTE: Configuring authentication for SERVER mode.

Enter the email address and password to use for the initial pgAdmin user account:

Email address: [e-mail]
Password: 
Retype password: 

The configuration database has been created at /home/titi/.pgadmin/pgadmin4.db
Starting pgAdmin 4. Please navigate to http://localhost:5050 in your browser.

 

Aide : https://stackoverflow.com/questions/41260004/error-trying-to-run-pgadmin4

.pgpass

Utilisation du .pgpass :

https://newfivefour.com/postgresql-pgpass-password-file.html

 

attention l'utilisation du .pgpass ne fonctionne pas avec une connexion LDAP :

psql: FATAL:  authentification LDAP échouée pour l'utilisateur « titi»

 

 

 

Localiser ses données en Postgres

dev=# select oid,datname from pg_database;
  oid  |  datname  
-------+-----------
 13275 | postgres
     1 | template1
 13274 | template0
 16388 | dev
(4 lignes)

dev=# q
[postgres@titi 16388]$ ls -l $PGDATA/base
total 24
drwx------ 2 postgres users  4096 25 août  16:28 1
drwx------ 2 postgres users  4096 25 août  16:28 13274
drwx------ 2 postgres users  4096  4 oct.  11:01 13275
drwx------ 2 postgres users 12288  4 oct.  11:00 16388

Trouver un objet particulier en Postgres

select oid,relfilenode,relname from pg_class where relfilenode='16826';




  oid  | relfilenode |        relname        
-------+-------------+-----------------------
 16804 |       16826 | table_toto




ou

dev=# select oid,relfilenode,relname from pg_class where relname='table_toto';

 

Afficher les fonctions internes PostgreSQL

Pour afficher les fonctions internes présentes sur le système :

db=#select * from pg_proc;

Afficher les verrous

SELECT pg_class.relname, pg_locks.* FROM pg_locks, pg_class WHERE pg_locks.relation = pg_class.oid;

Optimiser requete sql pour postgres

Installer au préalable le module : pg_stats_statements

Exécuter la requête suivante :

SELECT * FROM pg_stat_statements ORDER BY total_time DESC;