On tire la chasse et on nettoie ?

PostgreSQL - vacuumdb - vacuumdb — ramasse les ordures et analyse une base de données PostgreSQL Synopsi - Français (runebook.dev)

Calcul stats PostgreSQL

for tablename in $(psql -U postgres -h $2 -d $1 -t -c "select table_name as _table from information_schema.tables t where t.table_schema='sche_admin' order by _table asc"); do

  echo $tablename

  psql -U postgres -h $2 -d $1 -c "analyze ${tablename};"

  psql -U postgres -h $2 -d $1 -c "select count(*) ${tablename} from ${tablename};" >> compte_pgsql.log

done

 

En // :

vacuumdb --port=$PORT --username=$PG_USER --analyze-only --verbose --jobs=4  $PG_DATABASE

[PostgreSQL] pgBackRest sauvegarder la standby

Backup From a Standby | pgBackRest (pmatseykanets.github.io)

[PostgreSQL] Vérifier le statut de la réplication

On master:

select * from pg_stat_replication;

On replica (streaming replication in my case):

select * from pg_stat_wal_receiver;

On your master, pg_stat_replication provides data about ongoing replication:

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

On postgresql v10:

select client_addr, state, sent_lsn, write_lsn,
    flush_lsn, replay_lsn from pg_stat_replication;

On server

postgres=# select usename,application_name,client_addr,backend_start,state,sync_state from pg_stat_replication ;


On client

postgres=# select pg_is_in_recovery();

postgres=# select pg_last_xlog_receive_location();

postgres=#    SELECT CASE WHEN pg_last_xlog_receive_location() = pg_last_xlog_replay_location()
                  THEN 0
                ELSE EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp())
              END AS log_delay;

postgres=# select pg_last_xlog_replay_location();

[PostgreSQL] COMPRESS

Detecting inline, inline-compressed and TOAST storage

+--+--------------+------------------+----------+-----------+-------------+
|id|full_name     |uncompressed_bytes|compressed|out_of_line|bytes_on_disk|
+--+--------------+------------------+----------+-----------+-------------+
|1 |joe toast     |4004              |false     |true       |4000         |
|2 |joe compressed|3000              |true      |false      |44           |
|3 |joe inline    |10                |false     |false      |11           |

Deploy new postgreSQL instance dMZR

Deploy new postgreSQL instance dMZR

Modifier l'encodage PostgreSQL en UTF8

How to change PostgreSQL database encoding to UTF8 (shubhamdipt.com)

 

Running Health Checks Manually

Managing Diagnostic Data (oracle.com)

Installation Oracle19c

Installation Oracle 19c

PostgreSQL bonnes pratiques

Restriction du schéma « public »
Définir des nouveaux tablespaces pour les données et indexes et temp et changer le nom du tablespace par défaut (data et temp)