I wanted to try GoldenGate replication with PostgreSQL. The extract and
data pump processes should be on the Oracle side and the "replicat"
(apply) process should be on the Postgres side. However, DBLOGIN
SOURCEDB <dsn> USERID <usr> PASSWORD <pwd> fails with the following error:
GGSCI (pgsql16.home.com) 1> dblogin sourcedb pgsql13 userid ggadmin
password qwerty
2024-01-10 12:44:40 WARNING OGG-00552 Database operation failed:
Couldn't connect to pgsql13. ODBC error: SQLSTATE 587 native database
error 0. 523 586.
Error: Database operation failed: Couldn't connect to pgsql13. ODBC
error: SQLSTATE 587 native database error 0. 523 586Failed to open data
source pgsql13 for user ggadmin
PostgreSQL version is version 13 which should be supported for GG 21c.
postgres=# select version();
version
--------------------------------------------------------------------------------
---------------------------
PostgreSQL 13.13 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.4.1
20230605
(Red Hat 11.4.1-2), 64-bit
(1 row)
At least the release notes say so. ODBC driver, used for the database
connection, works like a charm:
[postgres@pgsql16 data]$ isql pgsql13 scott tiger
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
My ODBCINI is in the PATH, LD_LIBRARY_PATH is set according to
instructions. I am currently looking for work and cannot open a SR. My
main suspect is the ODBC driver:
[postgres@pgsql16 data]$ rpm -qa|grep postgres
pcp-pmda-postgresql-6.0.5-4.0.1.el9.x86_64
tuned-profiles-postgresql-2.21.0-1.0.1.el9_3.noarch
postgresql13-libs-13.13-1PGDG.rhel9.x86_64
postgresql13-13.13-1PGDG.rhel9.x86_64
postgresql13-server-13.13-1PGDG.rhel9.x86_64
postgresql13-contrib-13.13-1PGDG.rhel9.x86_64
_*postgresql13-odbc-16.00.0000-1PGDG.rhel9.x86_64*_
postgresql13-llvmjit-13.13-1PGDG.rhel9.x86_64
All examples I've seen were with Data Direct ODBC drivers. I installed
the original ODBC driver, which comes with the Postgres software. The
whole rig is running on top of OL 9.3. I've attached my odbc.ini
Does anyone have experience with this kind of replication? I could use
some help. I will download Data Direct ODBC driver but would like to
know whether I'm looking in the right direction.
Regards
--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com
[ODBC]
InstallDir=/usr/pgsql-13/lib
IANAAppCodePage=4
[pgsql13]
Description = Test to Postgres
Driver = /usr/pgsql-13/lib/psqlodbc.so
Trace = Yes
TraceFile = sql.log
Database = scott
Servername = localhost
UserName = ggadmin
LogonID = ggadmin
Password = qwerty
Port = 5432
Protocol = 7.1
ReadOnly = No
RowVersioning = No
ShowSystemTables = No
ShowOidColumn = No
FakeOidIndex = No
ConnSettings =