Synonym support on PostgreSQL

Question

How to create and use Synonyms on PostgreSQL as in Oracle. Do I need to create some DB link or any thing else. I could not find any good official doc on this topic.

Edit 1

Actually as of now i have an application which has two separate modules which connects with two different oracle databases; One modules need to access tables of other so for which we use synonyms over db link in oracle. Now we are migrating application to postgresql, so we need synonyms.

Edit 2 When i say two different oracle databases it means it can be two different oracle instances or two schemas of same db, it is configurable in application and application must support both modes.

PostgreSQL version: 9.6.3

Answer 1

Approach 1:-

Finally i got it working using foreign data wrapper postgres_fdw as below I have two databases named dba and dbb. dbb has a table users and i need to access it in dba

CREATE SERVER myserver FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'localhost', dbname 'dbb', port '5432');



CREATE USER MAPPING FOR postgres  
SERVER myserver  
OPTIONS (user 'user', password 'password'); 

CREATE FOREIGN TABLE users (  
username char(1))
SERVER myserver  
OPTIONS (schema_name 'public', table_name 'users'); 

CREATE FOREIGN TABLE users (users char(1));

Now i can execute all select/update queries in dba.

Approach 2:- Can be achieved by creating two schemas in same db, below are the steps:

1. create two schemas ex app_schema, common_schema.

2. Grant access:

   GRANT CREATE,USAGE ON SCHEMA app_schema TO myuser;
   GRANT CREATE,USAGE ON SCHEMA common_schema TO myuser;
   

3. Now set search path of user as below

   alter user myuser set search_path to app_schema,common_schema;
   

Now tables in common_schema will be visible to myuser. For example let say we have a table user in common_schema and table app in app_schema then below queries will be running easily:

select * from user;
select * from app;

This is similar to synonyms in oracle.

Note- Above queries will work PostgreSQL 9.5.3+

Answer 2

I think you don't need synonyms in Postgres the way you need them in Oracle because unlike Oracle there is a clear distinction between a user and a schema in Postgres. It's not a 1:1 relationship and multiple users can easily use multiple schemas without the need to fully qualify the objects by exploiting Postgres' "search path" feature -  mydb.public.mytable.

Answer 3

If the tables are supposed to be in a different database in PostgreSQL as well, you'd create a foreign table using a foreign data wrapper.

If you used the Oracle synonym just to avoid having to write atable@dblink, you don't have to do anything in PostgreSQL, because foreign tables look and feel just like local tables in PostgreSQL.

If you use the synonym for some other purposes, you can either set search_path to include the schema where the target table is, or you can create a simple view that just selects everything from the target table.