Introduction

PostgreSQL

su - postgres
psql

\q

\c database_name  connect to db
\d   Lister les Tables

\d table_name


Tables sans primary key pour la db courante

SELECT 
    t.relname AS table_name
FROM 
    pg_class t
LEFT JOIN 
    pg_index pk ON pk.indrelid = t.oid AND pk.indisprimary = true
WHERE 
    t.relkind = 'r'  -- Filtrer uniquement les tables (pas les vues, etc.)
    AND pk.indexrelid IS NULL  -- Aucune clé primaire associée
    AND t.relname NOT LIKE 'pg_%'  -- Exclure les tables système
    AND t.relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public')  -- Optionnel, spécifier un schéma (par défaut 'public')
ORDER BY 
    t.relname;


	
	
Tables sans primary key pour toutes les DB	
	
SELECT 
    t.relname AS table_name
FROM 
    pg_class t
LEFT JOIN 
    pg_index pk ON pk.indrelid = t.oid AND pk.indisprimary = true
WHERE 
    t.relkind = 'r'  -- Cela filtre pour n'inclure que les tables (pas les vues, etc.)
    AND pk.indexrelid IS NULL  -- Aucune clé primaire associée
    AND t.relname NOT LIKE 'pg_%'  -- Exclure les tables système
ORDER BY 
    t.relname;
	
	
	
	INDEX TYPE SUR UNE DB
	
SELECT
        t.relname AS table_name,
        i.relname AS index_name,
        ix.indisunique AS is_unique,
        ix.indisprimary AS is_primary,
        am.amname AS index_type
    FROM
       pg_class t
        JOIN pg_index ix ON t.oid = ix.indrelid
        JOIN pg_class i ON i.oid = ix.indexrelid
        JOIN pg_am am ON i.relam = am.oid
    WHERE
        t.relkind = 'r'  -- Pour inclure uniquement les tables (pas les vues, etc.)
        AND t.relname NOT LIKE 'pg_%'  -- Exclure les tables système
   ORDER BY
        t.relname, i.relname;
	



REPLICATION TECNNIQUES	
		
postgres=# SELECT * FROM pg_stat_replication;
  pid   | usesysid |   usename   | application_name |  client_addr  | client_hostname | client_port |         backend_start
   | backend_xmin |   state   | sent_location | write_location | flush_location | replay_location | sync_priority | sync_state
--------+----------+-------------+------------------+---------------+-----------------+-------------+----------------------------
---+--------------+-----------+---------------+----------------+----------------+-----------------+---------------+------------
152004 |    16595 | replication | walreceiver      | 217.19.62.228 |                 |       37794 | 2024-11-12 10:29:28.726911+
01 |              | streaming | 15/D9C5AEF8   | 15/D9C5AEF8    | 15/D9C5AEF8    | 15/D9C59368     |             0 | async
(1 ligne)






/var/lib/pgsql/15/data/postgresql.conf : Main Configuration file (postgresql 15)


PostgreSQL Console


SELECT version();  display version details