En poursuivant votre navigation sur ce site, vous acceptez l'utilisation de cookies. Ces derniers assurent le bon fonctionnement de nos services. En savoir plus.
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
Backup From a Standby | pgBackRest (pmatseykanets.github.io)
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 serverpostgres=# 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();+--+--------------+------------------+----------+-----------+-------------+
|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
How to change PostgreSQL database encoding to UTF8 (shubhamdipt.com)
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)
Script de création des roles HVAULT dans ibm cloud pour postgreSQL (dMZR);
SELECT lo_unlink(oid) FROM pg_largeobject_metadata;
WARNING: out of shared memory
ERROR: out of shared memory
HINT: You might need to increase max_locks_per_transaction.
select sch.nspname as schemaname,
tab.relname as tablename,
col.attname as columnname,
col.attnum as columnnumber,
col.atttypid,
typ.typname as columntype
from pg_attribute col
join pg_type typ on col.atttypid = typ.oid
join pg_class tab on col.attrelid = tab.oid
join pg_namespace sch on tab.relnamespace = sch.oid
left join pg_attrdef def on tab.oid = def.adrelid
and col.attnum = def.adnum
left join pg_depend deps on def.oid = deps.objid
and deps.deptype = 'N'
where sch.nspname != 'information_schema'
and sch.nspname not like 'pg_%' -- won't work if you have user schemas matching pg_
and col.attnum > 0
and typ.typcategory = 'N'
and col.attnotnull = true
and typ.typname = 'int4'
order by sch.nspname,
tab.relname,
col.attnum;
with seqs as
(select oid,
relname as sequencename
from pg_class
where relkind = 'S' )
select sch.nspname as schemaname,
tab.relname as tablename,
col.attname as columnname,
col.attnum as columnnumber,
col.atttypid,
typ.typname as columntype,
seq.sequencename
from pg_attribute col
join pg_type typ on col.atttypid = typ.oid
join pg_class tab on col.attrelid = tab.oid
join pg_namespace sch on tab.relnamespace = sch.oid
left join pg_attrdef def on tab.oid = def.adrelid
and col.attnum = def.adnum
left join pg_depend deps on def.oid = deps.objid
and deps.deptype = 'N'
left join seqs seq on deps.refobjid = seq.oid
where sch.nspname != 'information_schema'
and sch.nspname not like 'pg_%' -- won't work if you have user schemas matching pg_
and col.attnum > 0
and typ.typcategory = 'N'
and col.attnotnull = true
order by sch.nspname,
tab.relname,
col.attnum;
select sch.nspname as schemaname,
tab.relname as tablename,
col.attname as columnname,
col.attnum as columnnumber,
col.atttypid,
typ.typname as columntype
from pg_attribute col
join pg_type typ on col.atttypid = typ.oid
join pg_class tab on col.attrelid = tab.oid
join pg_namespace sch on tab.relnamespace = sch.oid
left join pg_attrdef def on tab.oid = def.adrelid
and col.attnum = def.adnum
left join pg_depend deps on def.oid = deps.objid
and deps.deptype = 'N'
where sch.nspname != 'information_schema'
and sch.nspname not like 'pg_%' -- won't work if you have user schemas matching pg_
and col.attnum > 0
and typ.typcategory = 'N'
and col.attnotnull = true
and typ.typname = 'int4'
order by sch.nspname,
tab.relname,
col.attnum;
with seqs as
(select oid,
relname as sequencename
from pg_class
where relkind = 'S' )
select sch.nspname as schemaname,
tab.relname as tablename,
col.attname as columnname,
col.attnum as columnnumber,
col.atttypid,
typ.typname as columntype,
seq.sequencename
from pg_attribute col
join pg_type typ on col.atttypid = typ.oid
join pg_class tab on col.attrelid = tab.oid
join pg_namespace sch on tab.relnamespace = sch.oid
left join pg_attrdef def on tab.oid = def.adrelid
and col.attnum = def.adnum
left join pg_depend deps on def.oid = deps.objid
and deps.deptype = 'N'
left join seqs seq on deps.refobjid = seq.oid
where sch.nspname != 'information_schema'
and sch.nspname not like 'pg_%' -- won't work if you have user schemas matching pg_
and col.attnum > 0
and typ.typcategory = 'N'
and col.attnotnull = true
order by sch.nspname,
tab.relname,
col.attnum;