You might have already seen our previous articles supporting Oracle to PostgreSQL and SQL server to PostgreSQL migrations. Our previous articles have provided solutions to some of the complexities during Oracle to PostgreSQL migrations. In this article, we are going to discuss about migration of synonyms from Oracle to PostgreSQL. The major reason behind this article is that the concept of SYNONYMS is not supported in Postgres. We shall now see the interesting work-arounds available in Postgres for Synonyms in Oracle.
What is a Synonym in Oracle ?
A Synonym in Oracle is an alias to a database object. This is very helpful in Oracle when a user has to access objects from different schemas without qualifying the object with its schema name. To understand the concept of Synonyms in Oracle, let us consider a simple example.
In our example, we shall consider 2 schemas S1 and S2. There exists a table named T1 in Schema : S1 and a table named T2 in Schema : S2. To join both the tables, the user of the schema : S2, has to fully qualify the table T1 of schema : S1.
SELECT T1.ID, T2.BONUS FROM S1.T1 T1 JOIN T2 ON T1.ID = T2.EMP_ID; |
In some environments, there may be many such tables that belong to different schemas and a user might need to execute certain queries joining them. Each time, the user has to fully qualify the table including its schema and also have appropriate access granted to such tables individually.
A synonym in Oracle can help users create an alias in the Users schema that refers to an object in any schema. See the following syntax as an example. In the schema : S2, we can have a private synonym pointing to table T1 of Schema : S1
CREATE SYNONYM T1 FOR S1.T1; |
As the synonym is now created in the schema of the user, the user can now access the table of the other schema : S2.
SELECT T1.ID, T2.BONUS FROM T1 JOIN T2 ON T1.ID = T2.EMP_ID; |
Public and Private Synonyms in Oracle
A Public synonym is a synonym that is accessible to all users in an Oracle database. In order to create a public synonym, the keyword PUBLIC has to be used. A private synonym is created in the schema of the user and is only accessible to that user or any other users that are granted appropriate privileges to that object.
– Public Synonym SQL> CREATE PUBLIC SYNONYM SS1 FOR S1.T1; – Private Synonym SQL> CREATE SYNONYM SS2 FOR S1.T1; |
Alternative to Synonym in PostgreSQL
An interesting point to understand is that there is no support for SYNONYM in PostgreSQL. However, it does not mean that the concept is not achievable. We can still create a work-around that allows us to achieve the exact same SYNONYM features of Oracle in PostgreSQL.
Following are the 2 approaches that can be leveraged to migrate SYNONYMs to PostgreSQL.
- Leveraging search_path to point users to objects in appropriate schemas
- Migrating Synonyms as VIEWS in PostgreSQL
Looking to Migrate from Oracle to PostgreSQL ? Contact MigOps today.
Creating an alias using search_path in PostgreSQL
In PostgreSQL, there exists a parameter called search_path. This parameter solves the purpose of avoiding qualified table names at each stage. See the following default setting for search_path to understand it in detail.
postgres=# show search_path ; search_path ----------------- "$user", public (1 row) |
With the above default setting for search_path, when a user refers to an unqualified object, the object is searched within the schemas mentioned in the search_path. “$user” in this case is the user that established the connection. So, in this case, Postgres searches for the object in the schema with the same name as the user (if it exists). If the schema is not found or the object referred by the user is not found in the first schema, then, postgres searches for the object in the public schema. This is because public is the second schema mentioned in the default search_path.
See the following example where a table t1 is created in schema : s1 and a query on the unqualified table has failed.
postgres=# CREATE SCHEMA s1; CREATE SCHEMA postgres=# CREATE TABLE s1.t1 (id int); CREATE TABLE postgres=# show search_path ; search_path ----------------- "$user", public (1 row) postgres=# SELECT count(*) from t1; ERROR: relation "t1" does not exist LINE 1: SELECT count(*) from t1; |
Let us set the search_path to schema : s1 and see if the query using an unqualified table name is successful.
postgres=# SET search_path TO "$user", s1, public; SET postgres=# SELECT count(*) from t1; count ------- 10 (1 row) |
From the above log, it is clear that adding the schema to the search_path has helped us avoid writing a qualified table name. In the similar fashion, we can consider the above example we have used for Oracle synonyms.
postgres=# SET search_path TO "$user", s1, s2, public; SET postgres=# SELECT T1.ID, T2.BONUS FROM T1 JOIN T2 ON T1.ID = T2.EMP_ID; id | bonus ----+------- 1 | 100000 …………………………………. (10 rows) |
The search_path in PostgreSQL can be set at following levels -
- Global - Applicable for all connections. Can be set by configuring search_path parameter in postgresql.conf or the other Postgres configuration files.
postgres=# ALTER SYSTEM SET search_path TO '"$user", s1, s2, public'; ALTER SYSTEM postgres=# show search_path ; search_path ------------------------- "$user", s1, s2, public (1 row) |
- User - Applicable for connections established by a specific user.
In the following example, we are setting search_path to user : migops as s1, s2, public. When we now connect as user : migops, it would search for those tables in s1 and then in s2 and then in public schemas.
postgres=# ALTER USER migops SET search_path TO s1,s2,public; ALTER ROLE $ psql -d postgres -U migops -h localhost -c "select current_user, count(*) from t2" current_user | count --------------+------- migops | 10 |
- Session - Applicable for the specific session to which the search_path is being set.
postgres=# SET search_path TO "$user", s1, s2, public; SET postgres=# SELECT count(*) from t1; count ------- 10 (1 row) |
Migrating Synonyms as Views in PostgreSQL
In certain scenarios, there may be Synonyms in Oracle created with different names when an object already exists with the same name as the object the synonym is referring to. For example, there may be a table called : T1 in schema : S1 and another table with the same name : T1 in schema : S2. In this case, we can have a Synonym created as T11 in Schema : S1 that points to table : T1 in schema : S2.
The above discussed scenario is complicated to achieve with the help of search_path. In such a case, we can have a VIEW in Postgres that could replace a Synonym in Oracle.
– Oracle Synonym CREATE SYNONYM T11 FOR S2.T1; – PostgreSQL View CREATE VIEW s1.t11 as SELECT * FROM s2.t1; |
In the above example, we could see how a Synonym is rather migrated to a VIEW in PostgreSQL because of the conflict in object names.
Looking to Migrate from Oracle to PostgreSQL ? Contact MigOps today.
Conclusion
We have seen how Synonyms in Oracle can be safely migrated to PostgreSQL using multiple methods. It is easy to directly migrate Synonyms to VIEWS when compared to leveraging search_path for the same functionality. If you are using Ora2Pg or any other migration tool, most of them migrate Oracle Synonyms as Views in PostgreSQL. If you are in the process of migrating from Oracle to PostgreSQL or SQL Server to PostgreSQL and need guidance, please contact MigOps and we shall be happy to help you. You may also fill the following form and one of our team members will be in touch with you.
