Results for topic "database":
Known issue with Database Updates
… Affected Releases The following releases are affected by the issue: 2021.2.2 2021.2.1 2021.2.0 2021.1.2 2020.3.6 2020.3.5 2020.2.7 The issue can occur with a ce …
https://documentation.censhare.com/censhare-2021/en/what-s-new-in-censhare/known-issue-with-database-updates
Database setup
… The platform’s underlying semantic database structure saves all structured information and metadata in objects, along with their relationships with each other. …
https://documentation.censhare.com/censhare-2021/en/administrator-guide/setup/database-setup
Cheatsheet - Oracle and PostgresSQL
… Differences in daily work with Oracle and PostgreSQL databases. Task Oracle PostgreSQL WARNING: commit differences by default: SQL> show autocommit autocommit O<><><><> …
https://documentation.censhare.com/censhare-2021/en/administrator-guide/setup/database-setup/cheatsheet-oracle-and-postgressql
Migration PostgreSQL → Oracle
… Create a new default schema in Oracle, customize it and push the data directly from PostgreSQL without intermediate export files by using oracle_fdw (foreign da<><><><><><><><> …
https://documentation.censhare.com/censhare-2021/en/administrator-guide/setup/database-setup/migration-postgresql-oracle
Number of database connections - Configuration & troubleshooting
… Best practices of setting up and operating the database. How many database connections are required? The maximum number of open database connections depends on
https://documentation.censhare.com/censhare-2021/en/administrator-guide/setup/database-setup/number-of-database-connections-configuration-troubleshooting
Oracle Extended Data Types
… Extended Data Types is a feature as of Oracle 12c. It allows to increase the maximum size of VARCHAR2, NVARCHAR2, and RAW columns in a table. Prerequisites Orac
https://documentation.censhare.com/censhare-2021/en/administrator-guide/setup/database-setup/oracle-extended-data-types
Export Oracle database
… Execution time: 5 minutes + export wait time = 10-40 minutes for a standard censhare database. Prerequisites ssh access to the database server as user in Oracle<
https://documentation.censhare.com/censhare-2021/en/administrator-guide/setup/database-setup/export-oracle-database
Oracle compression licensing
… As an Enterprise Edition is needed for key compression as of version 12c, the "compress" instruction is now removed from create-db.sql script. It cannot be used …
https://documentation.censhare.com/censhare-2021/en/administrator-guide/setup/database-setup/oracle-compression-licensing
Import Oracle database
… Execution time: 5 minutes + copy dumpfile time + import wait time = 20-60 minutes for a standard censhare database. Prerequisites ssh access to the database ser<
https://documentation.censhare.com/censhare-2021/en/administrator-guide/setup/database-setup/import-oracle-database
Enhance security in Oracle database environments
… Separate users and permissions for the application and the application's updates to enhance security. Context Change of the deprecated synonym.sql environment t …
https://documentation.censhare.com/censhare-2021/en/administrator-guide/setup/database-setup/enhance-security-in-oracle-database-environments
Migration Oracle → PostgreSQL
… Create a new default schema in PostgreSQL, customize it and pool the data directly from Oracle without intermediate export files by using oracle_fdw (foreign da<5.7 because current_timestamp is a reserved word in postgresql: # only for <= v5.7 set search_path='public'; alter table stored_transformation drop column current_timestamp; Final check to see if there is any difference in the schemas: SELECT table_name, column_name FROM information_schema.columns WHERE table_schema='public' and column_name not in ('rowid') EXCEPT SELECT table_name, column_name FROM information_schema.columns WHERE table_schema='corpus' order by 1,2; SELECT table_name, column_name FROM information_schema.columns WHERE table_schema='corpus' and table_name not in ('plan_table','plan_view', 'timetrackerinterface') EXCEPT SELECT table_name, column_name FROM information_schema.columns WHERE table_schema='public' order by 1,2; Let PostgreSQL pool the data from Oracle For big databasese, you should extend the undo retention in Oracle, otherwise your import shall fail with error message ORA-01555: oracle@oracle-host $ sqlplus / as sysdba SQL><=5.7 postgres@postgresql-host $ psql -d corpus -U corpus alter table stored_transformation add column curr_timestamp TIMESTAMP(3); drop function get_columns(varchar); # Additional command, if we have to rename the column "current_timestamp" alter table stored_transformation rename column "current_timestamp" to curr_timestamp; psql -d corpus -U corpus DROP USER MAPPING FOR corpus SERVER oradb; \c corpus postgres DROP SERVER oradb cascade; DROP schema corpus; icon related-topics Optionally, assign a Font Awesome icon to the page. Enter the class attribute without quotation marks. Create a new default schema in PostgreSQL, customize it and pool the data directly from Oracle without intermediate export files by using oracle_fdw (foreign data wrapper). Current state of this solution Works as expected, but very slow because one of the biggest tables asset_feature have a CLOB column and OCI library doesn't allow PostgreSQL to prefetch the rows. For example, a pull of a 40 Million rows table takes 3 hours. There are different workarounds that are described in our internal PostgreSQL mailing list. Install PostgreSQL For more information, see PostgreSQL installation. Setup and test Oracle Instant Client oracle_fdw is a PostgreSQL extention that need Oracle libraries. There are two optoins for you to take these libraries, download them form Oracle Website or get them from our FileServer. There is a third option to use the libraries from the Oracle DB itself ($ORACLE_HOME), but we shall not describe it here. Option 1: Download directly from Oracle Select your OS version here http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html. Then download and unzip the following files in ~postgres/ (the files shall create it's own subdirectory). Don't unzip them on Mac, because you shall get different directories. All files from every zip should be in the same directory. instantclient-basic-linux.x64-12.1.0.2.0.zip - Instant Client Package - Basic instantclient-sqlplus-linux.x64-12.1.0.2.0.zip - Instant Client Package - SQL*Plus instantclient-sdk-linux.x64-12.1.0.2.0.zip - Instant Client Package - SDK Option 2: Copy the package from our internal FileServer Execute the following command: rsync -avx /Volumes/Files/censhare/censhare-3rdParty/sqlplus/instantclient_12_1_linux/ postgres@PG-host:instantclient_12_1/ Setup and test connectivity to Oracle Modify and add the following variables in ~postgres/.pgsql_profile and ~postgres/.bash_profile: export ORACLE_SID=corpus export ORACLE_HOME=/var/lib/pgsql/instantclient_12_1 export PATH=/usr/pgsql-9.6/bin:$ORACLE_HOME:$PATH export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME Test connectivity to Oracle: postgres@pg-host $ sqlplus system/password@oracle-host:1521/corpus # if get an error mesage for a missing library, install it like this: # yum install libaio.x86_64 Modify environment inside the PostgreSQL startup script: # as root, search for PGDATA in that file and add the Oracle variables under the [Service] section of the file. vi /usr/lib/systemd/system/postgresql-9.6.service Environment=ORACLE_SID=corpus Environment=ORACLE_HOME=/var/lib/pgsql/instantclient_12_1 Environment=LD_LIBRARY_PATH=/var/lib/pgsql/instantclient_12_1 systemctl daemon-reload systemctl restart postgresql-9.6 Stop censhare process and prepare the Oracle database sqlplus corpus/passwrod -- truncate table messages; -- consider this optional step create table seqtmp(seqname varchar(100), lastnum integer, incr integer); insert into seqtmp select sequence_name,last_number,increment_by from user_sequences; commit;
-- optional: truncate table corpus.message; Setup oracle_fdw and connect PostgreSQL to Oracle Follow the basic installation instructions https://github.com/laurenz/oracle_fdw#6-installation. # as root, install needed libraries yum install postgresql96-devel.x86_64 chown -R postgres:postgres /usr/pgsql* # as postgres make soft link first cd /var/lib/pgsql/instantclient_12_1 ln -s libclntsh.so.12.1 libclntsh.so wget https://github.com/laurenz/oracle_fdw/archive/ORACLE_FDW_2_1_0.tar.gz tar -xvzf ORACLE_FDW_2_1_0.tar.gz && rm ORACLE_FDW_2_0_0.tar.gz cd oracle_fdw-ORACLE_FDW_2_1_0 make make install Sometimes, the follwing error occurs during the make install command: -bash-4.2$ make install /usr/bin/mkdir -p '/usr/pgsql-9.6/lib' /usr/bin/mkdir -p '/usr/pgsql-9.6/share/extension' /usr/bin/mkdir -p '/usr/pgsql-9.6/share/extension' /usr/bin/mkdir -p '/usr/pgsql-9.6/doc/extension' /usr/bin/install -c -m 755 oracle_fdw.so '/usr/pgsql9.6/lib/oracle_fdw.so' /usr/bin/install: cannot create regular file ‘/usr/pgsql-9.6/lib/oracle_fdw.so’: Permission deniedmake: *** [install-lib-shared] Error 1 To resolve the error, do the following: -bash-4.2$ pg_config --pgxs /usr/pgsql-9.6/lib/pgxs/src/makefiles/pgxs.mk Make sure that your pg_config path is in $PATH. To do this, use $PATH with the make install command, or change the permission of the pgsql-9.6 folder from "root" user to "postgres:postgres": [root@censhare-db usr]# chown -R postgres:postgres /usr/pgsql* Now the make install command should be run smoothly and show the following result: Switch to "postgres user" -bash-4.2$ cd oracle_fdw-ORACLE_FDW_2_0_0/ -bash-4.2$ make install /usr/bin/mkdir -p '/usr/pgsql-10/lib' /usr/bin/mkdir -p '/usr/pgsql-10/share/extension' /usr/bin/mkdir -p '/usr/pgsql-10/share/extension' /usr/bin/mkdir -p '/usr/pgsql-10/doc/extension' /usr/bin/install -c -m 755 oracle_fdw.so '/usr/pgsql-10/lib/oracle_fdw.so' /usr/bin/install -c -m 644 .//oracle_fdw.control '/usr/pgsql-10/share/extension/' /usr/bin/install -c -m 644 .//oracle_fdw--1.1.sql .//oracle_fdw--1.0--1.1.sql '/usr/pgsql-10/share/extension/' /usr/bin/install -c -m 644 .//README.oracle_fdw '/usr/pgsql-10/doc/extension/' -bash-4.2$ The following command connects the PostgreSQL daatbase to the Oracle database, and imports the foreign schema definitions: psql -d postgres \i postgresql-create-user.sql -- this script can be found under css/database/ on the app server and usualy have these two lines + few type cast definitinos -- CREATE USER corpus PASSWORD 'corpus'; -- CREATE DATABASE corpus OWNER corpus TEMPLATE template0 ENCODING 'UTF8'; \c corpus postgres CREATE EXTENSION oracle_fdw; CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//IP.OF.ORACLE.SERVER:1521/corpus.censhare'); GRANT USAGE ON FOREIGN SERVER oradb TO corpus; \c corpus corpus CREATE USER MAPPING FOR corpus SERVER oradb OPTIONS (user 'corpus', password 'password'); CREATE SCHEMA corpus; IMPORT FOREIGN SCHEMA "CORPUS" FROM SERVER oradb INTO CORPUS; -- in case that you would like to DROP and recreate it: -- psql -d postgres -- drop database corpus; -- drop user corpus; Create the new default PostgreSQL schema In our software release, PostgreSQL and Oracle have different schemas (data types, constraints, index options, stored procedures etc.). After the data migration, PostgreSQL should have the same schema as our release plus customer specific columns (language, db-schema.patch etc.). For that reason, we create here an empty default PostgreSQL schema taken from the release for that specific censhare server version. # First copy the schema definition file from this specific censhare server version. you@yourMac$ scp corpus@cs-srver:css/database/postgresql-create-db.sql /tmp/ you@yourMac$ scp /tmp/postgresql-create-db.sql postgres@pg-host: ## Then separate the TABLES structure from everything else perl -ne 'print if /^CREATE TABLE/../^\);/' postgresql-create-db.sql ><5.7 because current_timestamp is a reserved word in postgresql: # only for <= v5.7 set search_path='public'; alter table stored_transformation drop column current_timestamp; Final check to see if there is any difference in the schemas: SELECT table_name, column_name FROM information_schema.columns WHERE table_schema='public' and column_name not in ('rowid') EXCEPT SELECT table_name, column_name FROM information_schema.columns WHERE table_schema='corpus' order by 1,2; SELECT table_name, column_name FROM information_schema.columns WHERE table_schema='corpus' and table_name not in ('plan_table','plan_view', 'timetrackerinterface') EXCEPT SELECT table_name, column_name FROM information_schema.columns WHERE table_schema='public' order by 1,2; Let PostgreSQL pool the data from Oracle For big databasese, you should extend the undo retention in Oracle, otherwise your import shall fail with error message ORA-01555: oracle@oracle-host $ sqlplus / as sysdba SQL><=5.7 postgres@postgresql-host $ psql -d corpus -U corpus alter table stored_transformation add column curr_timestamp TIMESTAMP(3); drop function get_columns(varchar); # Additional command, if we have to rename the column "current_timestamp" alter table stored_transformation rename column "current_timestamp" to curr_timestamp; psql -d corpus -U corpus DROP USER MAPPING FOR corpus SERVER oradb; \c corpus postgres DROP SERVER oradb cascade; DROP schema corpus; icon related-topics Optionally, assign a Font Awesome icon to the page. Enter the class attribute without quotation marks. Create a new default schema in PostgreSQL, customize it and pool the data directly from Oracle without intermediate export files by using oracle_fdw (foreign data wrapper). Current state of this solution Works as expected, but very slow because one of the biggest tables asset_feature have a CLOB column and OCI library doesn't allow PostgreSQL to prefetch the rows. For example, a pull of a 40 Million rows table takes 3 hours. There are different workarounds that are described in our internal PostgreSQL mailing list. Install PostgreSQL For more information, see PostgreSQL installation. Setup and test Oracle Instant Client oracle_fdw is a PostgreSQL extention that need Oracle libraries. There are two optoins for you to take these libraries, download them form Oracle Website or get them from our FileServer. There is a third option to use the libraries from the Oracle DB itself ($ORACLE_HOME), but we shall not describe it here. Option 1: Download directly from Oracle Select your OS version here http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html. Then download and unzip the following files in ~postgres/ (the files shall create it's own subdirectory). Don't unzip them on Mac, because you shall get different directories. All files from every zip should be in the same directory. instantclient-basic-linux.x64-12.1.0.2.0.zip - Instant Client Package - Basic instantclient-sqlplus-linux.x64-12.1.0.2.0.zip - Instant Client Package - SQL*Plus instantclient-sdk-linux.x64-12.1.0.2.0.zip - Instant Client Package - SDK Option 2: Copy the package from our internal FileServer Execute the following command: rsync -avx /Volumes/Files/censhare/censhare-3rdParty/sqlplus/instantclient_12_1_linux/ postgres@PG-host:instantclient_12_1/ Setup and test connectivity to Oracle Modify and add the following variables in ~postgres/.pgsql_profile and ~postgres/.bash_profile: export ORACLE_SID=corpus export ORACLE_HOME=/var/lib/pgsql/instantclient_12_1 export PATH=/usr/pgsql-9.6/bin:$ORACLE_HOME:$PATH export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME Test connectivity to Oracle: postgres@pg-host $ sqlplus system/password@oracle-host:1521/corpus # if get an error mesage for a missing library, install it like this: # yum install libaio.x86_64 Modify environment inside the PostgreSQL startup script: # as root, search for PGDATA in that file and add the Oracle variables under the [Service] section of the file. vi /usr/lib/systemd/system/postgresql-9.6.service Environment=ORACLE_SID=corpus Environment=ORACLE_HOME=/var/lib/pgsql/instantclient_12_1 Environment=LD_LIBRARY_PATH=/var/lib/pgsql/instantclient_12_1 systemctl daemon-reload systemctl restart postgresql-9.6 Stop censhare process and prepare the Oracle database sqlplus corpus/passwrod -- truncate table messages; -- consider this optional step create table seqtmp(seqname varchar(100), lastnum integer, incr integer); insert into seqtmp select sequence_name,last_number,increment_by from user_sequences; commit;
-- optional: truncate table corpus.message; Setup oracle_fdw and connect PostgreSQL to Oracle Follow the basic installation instructions https://github.com/laurenz/oracle_fdw#6-installation. # as root, install needed libraries yum install postgresql96-devel.x86_64 chown -R postgres:postgres /usr/pgsql* # as postgres make soft link first cd /var/lib/pgsql/instantclient_12_1 ln -s libclntsh.so.12.1 libclntsh.so wget https://github.com/laurenz/oracle_fdw/archive/ORACLE_FDW_2_1_0.tar.gz tar -xvzf ORACLE_FDW_2_1_0.tar.gz && rm ORACLE_FDW_2_0_0.tar.gz cd oracle_fdw-ORACLE_FDW_2_1_0 make make install Sometimes, the follwing error occurs during the make install command: -bash-4.2$ make install /usr/bin/mkdir -p '/usr/pgsql-9.6/lib' /usr/bin/mkdir -p '/usr/pgsql-9.6/share/extension' /usr/bin/mkdir -p '/usr/pgsql-9.6/share/extension' /usr/bin/mkdir -p '/usr/pgsql-9.6/doc/extension' /usr/bin/install -c -m 755 oracle_fdw.so '/usr/pgsql9.6/lib/oracle_fdw.so' /usr/bin/install: cannot create regular file ‘/usr/pgsql-9.6/lib/oracle_fdw.so’: Permission deniedmake: *** [install-lib-shared] Error 1 To resolve the error, do the following: -bash-4.2$ pg_config --pgxs /usr/pgsql-9.6/lib/pgxs/src/makefiles/pgxs.mk Make sure that your pg_config path is in $PATH. To do this, use $PATH with the make install command, or change the permission of the pgsql-9.6 folder from "root" user to "postgres:postgres": [root@censhare-db usr]# chown -R postgres:postgres /usr/pgsql* Now the make install command should be run smoothly and show the following result: Switch to "postgres user" -bash-4.2$ cd oracle_fdw-ORACLE_FDW_2_0_0/ -bash-4.2$ make install /usr/bin/mkdir -p '/usr/pgsql-10/lib' /usr/bin/mkdir -p '/usr/pgsql-10/share/extension' /usr/bin/mkdir -p '/usr/pgsql-10/share/extension' /usr/bin/mkdir -p '/usr/pgsql-10/doc/extension' /usr/bin/install -c -m 755 oracle_fdw.so '/usr/pgsql-10/lib/oracle_fdw.so' /usr/bin/install -c -m 644 .//oracle_fdw.control '/usr/pgsql-10/share/extension/' /usr/bin/install -c -m 644 .//oracle_fdw--1.1.sql .//oracle_fdw--1.0--1.1.sql '/usr/pgsql-10/share/extension/' /usr/bin/install -c -m 644 .//README.oracle_fdw '/usr/pgsql-10/doc/extension/' -bash-4.2$ The following command connects the PostgreSQL daatbase to the Oracle database, and imports the foreign schema definitions: psql -d postgres \i postgresql-create-user.sql -- this script can be found under css/database/ on the app server and usualy have these two lines + few type cast definitinos -- CREATE USER corpus PASSWORD 'corpus'; -- CREATE DATABASE corpus OWNER corpus TEMPLATE template0 ENCODING 'UTF8'; \c corpus postgres CREATE EXTENSION oracle_fdw; CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//IP.OF.ORACLE.SERVER:1521/corpus.censhare'); GRANT USAGE ON FOREIGN SERVER oradb TO corpus; \c corpus corpus CREATE USER MAPPING FOR corpus SERVER oradb OPTIONS (user 'corpus', password 'password'); CREATE SCHEMA corpus; IMPORT FOREIGN SCHEMA "CORPUS" FROM SERVER oradb INTO CORPUS; -- in case that you would like to DROP and recreate it: -- psql -d postgres -- drop database corpus; -- drop user corpus; Create the new default PostgreSQL schema In our software release, PostgreSQL and Oracle have different schemas (data types, constraints, index options, stored procedures etc.). After the data migration, PostgreSQL should have the same schema as our release plus customer specific columns (language, db-schema.patch etc.). For that reason, we create here an empty default PostgreSQL schema taken from the release for that specific censhare server version. # First copy the schema definition file from this specific censhare server version. you@yourMac$ scp corpus@cs-srver:css/database/postgresql-create-db.sql /tmp/ you@yourMac$ scp /tmp/postgresql-create-db.sql postgres@pg-host: ## Then separate the TABLES structure from everything else perl -ne 'print if /^CREATE TABLE/../^\);/' postgresql-create-db.sql ><5.7 because current_timestamp is a reserved word in postgresql: # only for <= v5.7 set search_path='public'; alter table stored_transformation drop column current_timestamp; Final check to see if there is any difference in the schemas: SELECT table_name, column_name FROM information_schema.columns WHERE table_schema='public' and column_name not in ('rowid') EXCEPT SELECT table_name, column_name FROM information_schema.columns WHERE table_schema='corpus' order by 1,2; SELECT table_name, column_name FROM information_schema.columns WHERE table_schema='corpus' and table_name not in ('plan_table','plan_view', 'timetrackerinterface') EXCEPT SELECT table_name, column_name FROM information_schema.columns WHERE table_schema='public' order by 1,2; Let PostgreSQL pool the data from Oracle For big databasese, you should extend the undo retention in Oracle, otherwise your import shall fail with error message ORA-01555: oracle@oracle-host $ sqlplus / as sysdba SQL><=5.7 postgres@postgresql-host $ psql -d corpus -U corpus alter table stored_transformation add column curr_timestamp TIMESTAMP(3); drop function get_columns(varchar); # Additional command, if we have to rename the column "current_timestamp" alter table stored_transformation rename column "current_timestamp" to curr_timestamp; psql -d corpus -U corpus DROP USER MAPPING FOR corpus SERVER oradb; \c corpus postgres DROP SERVER oradb cascade; DROP schema corpus; icon related-topics Optionally, assign a Font Awesome icon to the page. Enter the class attribute without quotation marks. Create a new default schema in PostgreSQL, customize it and pool the data directly from Oracle without intermediate export files by using oracle_fdw (foreign data wrapper). Current state of this solution Works as expected, but very slow because one of the biggest tables asset_feature have a CLOB column and OCI library doesn't allow PostgreSQL to prefetch the rows. For example, a pull of a 40 Million rows table takes 3 hours. There are different workarounds that are described in our internal PostgreSQL mailing list. Install PostgreSQL For more information, see PostgreSQL installation. Setup and test Oracle Instant Client oracle_fdw is a PostgreSQL extention that need Oracle libraries. There are two optoins for you to take these libraries, download them form Oracle Website or get them from our FileServer. There is a third option to use the libraries from the Oracle DB itself ($ORACLE_HOME), but we shall not describe it here. Option 1: Download directly from Oracle Select your OS version here http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html. Then download and unzip the following files in ~postgres/ (the files shall create it's own subdirectory). Don't unzip them on Mac, because you shall get different directories. All files from every zip should be in the same directory. instantclient-basic-linux.x64-12.1.0.2.0.zip - Instant Client Package - Basic instantclient-sqlplus-linux.x64-12.1.0.2.0.zip - Instant Client Package - SQL*Plus instantclient-sdk-linux.x64-12.1.0.2.0.zip - Instant Client Package - SDK Option 2: Copy the package from our internal FileServer Execute the following command: rsync -avx /Volumes/Files/censhare/censhare-3rdParty/sqlplus/instantclient_12_1_linux/ postgres@PG-host:instantclient_12_1/ Setup and test connectivity to Oracle Modify and add the following variables in ~postgres/.pgsql_profile and ~postgres/.bash_profile: export ORACLE_SID=corpus export ORACLE_HOME=/var/lib/pgsql/instantclient_12_1 export PATH=/usr/pgsql-9.6/bin:$ORACLE_HOME:$PATH export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME Test connectivity to Oracle: postgres@pg-host $ sqlplus system/password@oracle-host:1521/corpus # if get an error mesage for a missing library, install it like this: # yum install libaio.x86_64 Modify environment inside the PostgreSQL startup script: # as root, search for PGDATA in that file and add the Oracle variables under the [Service] section of the file. vi /usr/lib/systemd/system/postgresql-9.6.service Environment=ORACLE_SID=corpus Environment=ORACLE_HOME=/var/lib/pgsql/instantclient_12_1 Environment=LD_LIBRARY_PATH=/var/lib/pgsql/instantclient_12_1 systemctl daemon-reload systemctl restart postgresql-9.6 Stop censhare process and prepare the Oracle database sqlplus corpus/passwrod -- truncate table messages; -- consider this optional step create table seqtmp(seqname varchar(100), lastnum integer, incr integer); insert into seqtmp select sequence_name,last_number,increment_by from user_sequences; commit;
-- optional: truncate table corpus.message; Setup oracle_fdw and connect PostgreSQL to Oracle Follow the basic installation instructions https://github.com/laurenz/oracle_fdw#6-installation. # as root, install needed libraries yum install postgresql96-devel.x86_64 chown -R postgres:postgres /usr/pgsql* # as postgres make soft link first cd /var/lib/pgsql/instantclient_12_1 ln -s libclntsh.so.12.1 libclntsh.so wget https://github.com/laurenz/oracle_fdw/archive/ORACLE_FDW_2_1_0.tar.gz tar -xvzf ORACLE_FDW_2_1_0.tar.gz && rm ORACLE_FDW_2_0_0.tar.gz cd oracle_fdw-ORACLE_FDW_2_1_0 make make install Sometimes, the follwing error occurs during the make install command: -bash-4.2$ make install /usr/bin/mkdir -p '/usr/pgsql-9.6/lib' /usr/bin/mkdir -p '/usr/pgsql-9.6/share/extension' /usr/bin/mkdir -p '/usr/pgsql-9.6/share/extension' /usr/bin/mkdir -p '/usr/pgsql-9.6/doc/extension' /usr/bin/install -c -m 755 oracle_fdw.so '/usr/pgsql9.6/lib/oracle_fdw.so' /usr/bin/install: cannot create regular file ‘/usr/pgsql-9.6/lib/oracle_fdw.so’: Permission deniedmake: *** [install-lib-shared] Error 1 To resolve the error, do the following: -bash-4.2$ pg_config --pgxs /usr/pgsql-9.6/lib/pgxs/src/makefiles/pgxs.mk Make sure that your pg_config path is in $PATH. To do this, use $PATH with the make install command, or change the permission of the pgsql-9.6 folder from "root" user to "postgres:postgres": [root@censhare-db usr]# chown -R postgres:postgres /usr/pgsql* Now the make install command should be run smoothly and show the following result: Switch to "postgres user" -bash-4.2$ cd oracle_fdw-ORACLE_FDW_2_0_0/ -bash-4.2$ make install /usr/bin/mkdir -p '/usr/pgsql-10/lib' /usr/bin/mkdir -p '/usr/pgsql-10/share/extension' /usr/bin/mkdir -p '/usr/pgsql-10/share/extension' /usr/bin/mkdir -p '/usr/pgsql-10/doc/extension' /usr/bin/install -c -m 755 oracle_fdw.so '/usr/pgsql-10/lib/oracle_fdw.so' /usr/bin/install -c -m 644 .//oracle_fdw.control '/usr/pgsql-10/share/extension/' /usr/bin/install -c -m 644 .//oracle_fdw--1.1.sql .//oracle_fdw--1.0--1.1.sql '/usr/pgsql-10/share/extension/' /usr/bin/install -c -m 644 .//README.oracle_fdw '/usr/pgsql-10/doc/extension/' -bash-4.2$ The following command connects the PostgreSQL daatbase to the Oracle database, and imports the foreign schema definitions: psql -d postgres \i postgresql-create-user.sql -- this script can be found under css/database/ on the app server and usualy have these two lines + few type cast definitinos -- CREATE USER corpus PASSWORD 'corpus'; -- CREATE DATABASE corpus OWNER corpus TEMPLATE template0 ENCODING 'UTF8'; \c corpus postgres CREATE EXTENSION oracle_fdw; CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//IP.OF.ORACLE.SERVER:1521/corpus.censhare'); GRANT USAGE ON FOREIGN SERVER oradb TO corpus; \c corpus corpus CREATE USER MAPPING FOR corpus SERVER oradb OPTIONS (user 'corpus', password 'password'); CREATE SCHEMA corpus; IMPORT FOREIGN SCHEMA "CORPUS" FROM SERVER oradb INTO CORPUS; -- in case that you would like to DROP and recreate it: -- psql -d postgres -- drop database corpus; -- drop user corpus; Create the new default PostgreSQL schema In our software release, PostgreSQL and Oracle have different schemas (data types, constraints, index options, stored procedures etc.). After the data migration, PostgreSQL should have the same schema as our release plus customer specific columns (language, db-schema.patch etc.). For that reason, we create here an empty default PostgreSQL schema taken from the release for that specific censhare server version. # First copy the schema definition file from this specific censhare server version. you@yourMac$ scp corpus@cs-srver:css/database/postgresql-create-db.sql /tmp/ you@yourMac$ scp /tmp/postgresql-create-db.sql postgres@pg-host: ## Then separate the TABLES structure from everything else perl -ne 'print if /^CREATE TABLE/../^\);/' postgresql-create-db.sql ><5.7 because current_timestamp is a reserved word in postgresql: # only for <= v5.7 set search_path='public'; alter table stored_transformation drop column current_timestamp; Final check to see if there is any difference in the schemas: SELECT table_name, column_name FROM information_schema.columns WHERE table_schema='public' and column_name not in ('rowid') EXCEPT SELECT table_name, column_name FROM information_schema.columns WHERE table_schema='corpus' order by 1,2; SELECT table_name, column_name FROM information_schema.columns WHERE table_schema='corpus' and table_name not in ('plan_table','plan_view', 'timetrackerinterface') EXCEPT SELECT table_name, column_name FROM information_schema.columns WHERE table_schema='public' order by 1,2; Let PostgreSQL pool the data from Oracle For big databasese, you should extend the undo retention in Oracle, otherwise your import shall fail with error message ORA-01555: oracle@oracle-host $ sqlplus / as sysdba SQL> …
https://documentation.censhare.com/censhare-2021/en/administrator-guide/setup/database-setup/migration-oracle-postgresql
Analyse database connections
… Learn about the best practices to analyze database connections. Configuration practices databaseconnections.jpeg Maximum open database connections to configure
https://documentation.censhare.com/censhare-2021/en/administrator-guide/operation/monitor-database-performamce/analyse-database-connections
Vacuum process for PostgreSQL databases
… Database vacuuming is a way to increase the table and database performance of a PostgreSQL database. Learn how to use the vacuum process to clean the database.
https://documentation.censhare.com/censhare-2021/en/administrator-guide/operation/monitor-database-performamce/vacuum-process-for-postgresql-databases
PostgreSQL database performance check
… Learn about the performance of a PostgreSQL database. Configure PostgreSQL for pretty good performance Every PostgreSQL database has a default configuration but<< EOF \pset format wrapped; \pset linestyle unicod; \pset columns 180; Select 'select pg_terminate_backend('||pid||') from pg_stat_activity;' kill_query,usename,state,client_addr,query,query_start From pg_stat_activity where datname='corpus' AND pid <>< current_timestamp - INTERVAL '30' MINUTE; EOF After running the above code, it provides "proc_id's" with other information in the "kill_query" column section and terminate the idle sessions by the following command. Run from "corpus" user to connect with "corpus" database. select pg_terminate_backend(
… How to restore the PostgreSQL database in case of disaster. Introduction We recommend to run a full backup of the database on every server, during the night by …<< EOF \pset format wrapped; \pset linestyle unicod; \pset columns 180; Select 'select pg_terminate_backend('||pid||') from pg_stat_activity;' kill_query,usename,state,client_addr,query,query_start From pg_stat_activity where datname='corpus' AND pid <>< current_timestamp - INTERVAL '30' MINUTE; EOF After running the above code, it provides "proc_id's" with other information in the "kill_query" column section and terminate the idle sessions by the following command. Run from "corpus" user to connect with "corpus" database. select pg_terminate_backend(
<< EOF \pset format wrapped; \pset linestyle unicod; \pset columns 180; Select 'select pg_terminate_backend('||pid||') from pg_stat_activity;' kill_query,usename,state,client_addr,query,query_start From pg_stat_activity where datname='corpus' AND pid <>< current_timestamp - INTERVAL '30' MINUTE; EOF After running the above code, it provides "proc_id's" with other information in the "kill_query" column section and terminate the idle sessions by the following command. Run from "corpus" user to connect with "corpus" database. select pg_terminate_backend(
<< EOF \pset format wrapped; \pset linestyle unicod; \pset columns 180; Select 'select pg_terminate_backend('||pid||') from pg_stat_activity;' kill_query,usename,state,client_addr,query,query_start From pg_stat_activity where datname='corpus' AND pid <>< current_timestamp - INTERVAL '30' MINUTE; EOF After running the above code, it provides "proc_id's" with other information in the "kill_query" column section and terminate the idle sessions by the following command. Run from "corpus" user to connect with "corpus" database. select pg_terminate_backend(
…
https://documentation.censhare.com/censhare-2021/en/administrator-guide/operation/monitor-database-performamce/postgresql-database-performance-check
Point-in-time recovery for PostgreSQL
https://documentation.censhare.com/censhare-2021/en/administrator-guide/operation/backup-and-restore/point-in-time-recovery-for-postgresql