PostgreSQL common commands ¶

dump de database ¶

Variables bash:

::

export YESTERDAY=`(date –date ‘1 days ago’ +%F)` export THREE_DAYS=`(date –date ‘3 days ago’ +%F)` export TODAY=`(date –date ‘today’ +%F)`

           date; export PGPASSWORD=${PGPASSWORD_STAGING_LOG}; pg_dump -v -h database-staging.srv.int.eu -d db_log -U log --format=plain > ~/Documents/db_log_${TODAY}.sql; date

          

Utilisation du format de sortie “plain” pour pouvoir effectuer d’éventuelles substitutions comme celles-ci par exemple (passage d’un schéma public à un schéma ‘log’):

           sed -i "s/^ALTER SCHEMA public OWNER TO postgres;/SET search_path = 'log';/" $1
sed -i 's/public[.]/log./g' $1
sed -i 's/Schema: public/Schema: log/g' $1
sed -i 's/REVOKE USAGE ON SCHEMA public FROM PUBLIC;/ /' $1
sed -i 's/GRANT ALL ON SCHEMA public TO PUBLIC;/ /' $1

          

create schema ¶

           export PGPASSWORD=${PGPASSWORD_STAGING}; psql -h database-staging.srv.int.eu -d db_intranet -U intranet -c "create schema intranet ;"

          

Modification du search_path pour le rôle log ¶

           export PGPASSWORD=${PGPASSWORD_STAGING_LOG}; psql -h database-staging.srv.int.eu -d db_log -U log -c "ALTER ROLE log SET search_path = log,public;"

          

Afficher le search_path ¶

           export PGPASSWORD=${PGPASSWORD_STAGING_LOG}; psql -h database-staging.srv.int.eu -d db_log -U log -c "show search_path"

          

            search_path
-------------
 public
(1 ligne)

          

conninfo ¶

           export PGPASSWORD=${PGPASSWORD_PRODUCTION}; psql -h database.srv.int.eu -d db_intranet -U intranet  -c "\conninfo"

          

drds (Lists defined configuration settings) ¶

Lists defined configuration settings

           export PGPASSWORD=${PGPASSWORD_STAGING}; psql -h database-staging.srv.int.eu -d db_intranet -U intranet -c "\drds ;"

          

                        Liste des paramètres
   Rôle   | Base de données |      Réglages
----------+-----------------+--------------------
 intranet |                 | search_path=public
(1 ligne)

          

dconfig (Lists server configuration parameters and their values) ¶

Lists server configuration parameters and their values

           export PGPASSWORD=${PGPASSWORD_STAGING}; psql -h database-staging.srv.int.eu -d db_intranet -U intranet -c "\dconfig;"

          

Ici on voit que database-staging.srv.int.eu est en version 14 (cluster name) .

           Liste des paramètres de configuration à valeur personnalisée
     Paramètre          |                 Valeur
----------------------------+----------------------------------------
application_name           | psql
client_encoding            | UTF8
cluster_name               | 14/main
DateStyle                  | ISO, DMY
default_text_search_config | pg_catalog.french
lc_collate                 | fr_FR.UTF-8
lc_ctype                   | fr_FR.UTF-8
lc_messages                | fr_FR.UTF-8
lc_monetary                | fr_FR.UTF-8
lc_numeric                 | fr_FR.UTF-8
lc_time                    | fr_FR.UTF-8
listen_addresses           | *
log_line_prefix            | %m [%p] %q%u@%d
log_timezone               | Europe/Paris
max_stack_depth            | 2MB
search_path                | public
server_encoding            | UTF8
shared_buffers             | 128MB
ssl                        | on
ssl_cert_file              | /etc/ssl/certs/ssl-cert-snakeoil.pem
ssl_key_file               | /etc/ssl/private/ssl-cert-snakeoil.key
TimeZone                   | Europe/Paris
wal_buffers                | 4MB
(23 lignes)

          

Import au format texte ¶

           date; export PGPASSWORD=${PGPASSWORD_STAGING_LOG}; psql -h database-staging.srv.int.eu -d db_log -U log  -c "drop schema log cascade" ; date
date; export PGPASSWORD=${PGPASSWORD_STAGING_LOG}; psql -h database-staging.srv.int.eu -d db_log -U log  -c "create schema log" ; date
date; export PGPASSWORD=${PGPASSWORD_STAGING_LOG}; psql -h database-staging.srv.int.eu -d db_log -U log  -c "\drds" ; date
date; export PGPASSWORD=${PGPASSWORD_STAGING_LOG}; psql -h database-staging.srv.int.eu -d db_log -U log -f ~/Documents/db_log_${TODAY}.sql; date

          

Alternative roles, search_path, show search_path ¶

           export PGPASSWORD=${PGPASSWORD_STAGING_LOG}; psql -h database-staging.srv.int.eu -d db_log -U log -c "ALTER ROLE log SET search_path = log;"
export PGPASSWORD=${PGPASSWORD_STAGING_LOG}; psql -h database-staging.srv.int.eu -d db_log -U log -c "show search_path"
export PGPASSWORD=${PGPASSWORD_STAGING_LOG}; psql -h database-staging.srv.int.eu -d db_log -U log -c "select * from log l where l.logdate >= '2022-12-18'"
export PGPASSWORD=${PGPASSWORD_STAGING_LOG}; psql -h database-staging.srv.int.eu -d db_log -U log -c "ALTER ROLE log SET search_path = public;"
export PGPASSWORD=${PGPASSWORD_STAGING_LOG}; psql -h database-staging.srv.int.eu -d db_log -U log -c "show search_path"
export PGPASSWORD=${PGPASSWORD_STAGING_LOG}; psql -h database-staging.srv.int.eu -d db_log -U log -c "select * from log l where l.logdate >= '2022-12-18'"

          