[POSTGRES] Privs d'un ROLE R/W
Exemple de création d'un rôle R/W avec les privileges.
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.
Exemple de création d'un rôle R/W avec les privileges.
select nspname
from pg_catalog.pg_namespace where nspname not like 'pg%' and nspname <> 'information_schema';
WITH "names"("name") AS (
SELECT n.nspname AS "name"
FROM pg_catalog.pg_namespace n
WHERE n.nspname !~ '^pg_'
AND n.nspname <> 'information_schema'
) SELECT "name",
pg_catalog.has_schema_privilege(current_user, "name", 'CREATE') AS "create",
pg_catalog.has_schema_privilege(current_user, "name", 'USAGE') AS "usage"
FROM "names";
Privs useful to any new user created.
#se connecter au serveur target
psql -U <muyser>
CREATE EXTENSION oracle_fdw;
dew
CREATE SERVER orasrv FOREIGN DATA WRAPPER oracle_fdw (dbserver '<servername>:<port>/<SID>');
des+
GRANT USAGE ON FOREIGN SERVER orasrv TO <muyser>;
CREATE USER MAPPING FOR <muyser> SERVER orasrv OPTIONS ( USER '<oracle_user>', PASSWORD 'xxxx');
deu+
CREATE FOREIGN TABLE ext_<ORATBL> ( id int OPTIONS (key 'true'), name varchar(64), t_data timestamp) SERVER orasrv OPTIONS (SCHEMA '<ORAUSER>' , TABLE '<ORATBL>');
tuto : https://www.postgresql.fastware.com/postgresql-insider-fdw-ora-bas
Pré requis :
- avoir une VM avec postgresql d'installé sur le meme VLAN qu'une base oracle avec Oracle instantclient-basic, instantclient-devel d'installé
- Ensure that pg_config is in your path, and ORACLE_HOME as well as LD_LIBRARY_PATH are configured
- avoir accès au compte postgres dans la bdd
- avoir accès à une base oracle en sysdba
- The <oracle_user> will obviously need CREATE SESSION privilege and the right to select from the table or view in question.
Bon à savoir : If a NUMBER is converted to a boolean, 0 means false, everything else true.
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 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();