[PostgreSQL] Privilèges
Privs useful to any new user created.
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.
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.
To be able to say “Yeah, we migrate to PostgreSQL”, we have to be confident by studying the technical aspects of it.
For that, we assess the feasibility of the migration from oracle to postgres.
The assessments consist in validating the :
For this, we use the free tool called ora2Pg.
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();
+--+--------------+------------------+----------+-----------+-------------+
|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