Join me on Google Pay, a secure app for money transfers, bills and recharges. Enter my code EE94w to earn ₹51 back on your first payment! https://g.co/payinvite/EE94w
Ora2Pg is a free tool used to migrate an Oracle or MySQL database to a PostgreSQL compatible schema. It connects your Oracle database, scan it automatically and extracts its structure or data, it then generates SQL scripts that you can load into your PostgreSQL database. ora2pg version 20.0 software : ora2pg-20.0.tar ora2pg-20.0 https://github.com/darold/ora2pg/releases/tag/v20.0 Some Blogs about DB Migration : https://2ndquadrant.in/postgresmigration/oracle-to-postgresql-migration/ https://2ndquadrant.in/database-migration-from-oracle-to-postgres/ INSTALLATION All Perl modules can always be found at CPAN ( http://search.cpan.org/ ). Just type the full name of the module (ex: DBD::Oracle) into the search input box, it will brings you the page for download. Releases of Ora2Pg stay at SF.net ( https://sourceforge.net/projects/ora2pg/ ). Under Windows you should install Strawberry Perl ( http://strawberryperl.com/ ) and the OSes corresponding Oracle clients. I
SQL> create pfile='/home/oracle/karan/admin/pfile/initclone.ora' from spfile; create pfile='/home/oracle/karan/admin/pfile/initclone.ora' from spfile * ERROR at line 1: ORA-01565: error in identifying file '?/dbs/spfile@.ora' ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3 SQL> show parameter spfile; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string SQL> create spfile= 2 SQL> create spfile='/home/oracle/karan/admin/pfile/initclone.ora' from pfile; create spfile='/home/oracle/karan/admin/pfile/initclone.ora' from pfile * ERROR at line 1: ORA-01078: failure in processing system parameters LRM-00109: could not open parameter file '/oraeng/app/oracle/product/11.2.0/dbs/initkiruba.ora' SQL> create spfile from pfile; create spfile from pfil
Method 1 You can check the mode of the server using "pg_controldata". [pgsql@test~]$ pg_controldata /usr/local/pgsql/data84/ Database cluster state: in archive recovery --> This is Standby Database Database cluster state: in production --> This is Production Database [Master] Method 2 You can use pg_is_in_recovery() which returns True if recovery is still in progress(so the server is running in standby mode or slave) postgres=# select pg_is_in_recovery(); pg_is_in_recovery ------------------- t (1 row) If Return false so the server is running in primary mode or master postgres=# select pg_is_in_recovery(); pg_is_in_recovery ------------------- f (1 row)
Both stored procedures and user-defined functions are created with CREATE FUNCTION statement in PostgreSQL. To return one or more result sets (cursors in terms of PostgreSQL), you have to use refcursor return type. Quick Example : -- Procedure that returns a single result set (cursor) CREATE OR REPLACE FUNCTION show_cities ( ) RETURNS refcursor AS $$ DECLARE ref refcursor; -- Declare a cursor variable BEGIN OPEN ref FOR SELECT city , state FROM cities; -- Open a cursor RETURN ref; -- Return the cursor to the caller END ; $$ LANGUAGE plpgsql; Overview : Return Multiple Result Sets Cursor Lifetime Until the end of transaction Auto-commit Must be off Transaction must be active so the caller can see a result set Important Note : The cursor remains open until the end of transaction, and since PostgreSQL works
In this blog we are going to upgrade the postgresql server from 9.5 to 11.3 . We are upgraded the postgres server by using pg_upgrade utility as well as logical backup method , you can follow anyone of the method . Collect the server details before proceed upgrade activity. Title PostgreSQL VERSION 9.5 PostgreSQL VERSION 11.3 DATA DIRECTORY /data_9.5 /data_11.3 BIN path /opt/postgresql/9.5 /opt/postgresql/11.3 PORT 50000 5432 PREREQUEST : =========== Step 1. Check the application backend connection if any application connected , Disconnect the application from DB server. Below command will be helpful to checking backend connection : template1=# select datname, count(*) from pg_stat_activity group by datname; datname | count -----------+------- testdb | 1 template1 | 0 (2 rows) template1=# select datname, numbackends from pg_stat_database; datname | numbackends -----------+------------- testdb | 1 template1 | 0 templ
Comments
Post a Comment