0

I need to create a sequence and need to use the sequence for auto increment id for more than 2 databases on 2 separate servers. I'm referring below doc for that(for test purpose): https://paquier.xyz/postgresql-2/global-sequences-with-postgres_fdw-and-postgres-core/

I have followed below steps:

    Server 1: 
CREATE DATABASE
postgres=# \c a1
You are now connected to database "a1" as user "postgres".
a1=# CREATE SEQUENCE seq;
CREATE SEQUENCE
a1=# CREATE VIEW seq_view AS SELECT nextval('seq') as a;
CREATE VIEW
a1=# \c postgres
You are now connected to database "postgres" as user "postgres".

postgres=# create database a2;
 CREATE DATABASE
 postgres=# \c a2;
You are now connected to database "a2" as user "postgres".
a2=# CREATE EXTENSION postgres_fdw;
CREATE EXTENSION
a2=# CREATE SERVER postgres_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS
a2-# (host '192.168.xx.xxx', port '5432', dbname 'a1');
CREATE SERVER
a2=# CREATE USER MAPPING FOR PUBLIC SERVER postgres_server OPTIONS (password 
'');
CREATE USER MAPPING
a2=# CREATE FOREIGN TABLE foreign_seq_table (a bigint) SERVER
a2-# postgres_server
a2-# OPTIONS (table_name 'seq_view');
CREATE FOREIGN TABLE
a2=# select * from foreign_seq_table;
 a
---
 1
(1 row)

a2=# select * from foreign_seq_table;
a
---
2
(1 row)

As it can be seen from above example, it's working properly for 2 databases on same server. However, when I proceed with another server, I did below steps there:

    postgres=# create database kbc;
CREATE DATABASE
 postgres=# \c kbc
You are now connected to database "kbc" as user "postgres".
kbc=#  CREATE EXTENSION postgres_fdw;
CREATE EXTENSION
kbc=# CREATE SERVER postgres_server FOREIGN DATA WRAPPER postgres_fdw 
OPTIONS
kbc-# (host '192.168.xx.xxx', port '5432', dbname 'a1');
CREATE SERVER
kbc=# CREATE USER MAPPING FOR PUBLIC SERVER postgres_server OPTIONS 
(password '');
CREATE USER MAPPING
kbc=#
kbc=# CREATE FOREIGN TABLE foreign_seq_table (a bigint) SERVER 
postgres_server OPTIONS (table_name 'seq_view');
  CREATE FOREIGN TABLE
 kbc=# select * from foreign_seq_table;
  ERROR:  could not connect to server "postgres_server"
    DETAIL:  fe_sendauth: no password supplied

The error at last I'm receiving. Is there anything/any step I'm missing here. I can easily ping to old server(where I created sequence) from new server(where I want to use that sequence). pg_hba.conf file settings on both server -> the entries there are md5 or trust only. Any other entry I need to add into configuration file? Any suggested doc for fdw across different servers will also be helpful.

Thanks in advance!

Note: I can't use UUID due to some application requirement. That's why we need an auto increment numeric column. It picks the highest id value.

3
  • @LaurenzAlbe : Could you suggest any other idea to create global sequence across multiple servers. My actual scenario is like this: we have 2 databases on 2 separate servers and both of them have same schema. There is a table on those 2 databases which should share the same sequence as id. Any other way to implement this?
    – Shiwangini
    Nov 25, 2019 at 13:26
  • 1
    Using a FDW is ok. A simpler method is to have a sequence with STARTS WITH 1 INCREMENT 2 and one with STARTS WITH 2 INCREMENT 2. Then they cannot collide. Nov 25, 2019 at 14:48
  • @LaurenzAlbe - I have got this problem resolved , when in create server command, instead of localhost , I supplied actual hostname and in create user mapping , I updated command as below: CREATE USER MAPPING FOR PUBLIC SERVER postgres_server OPTIONS (password ''); I didn't supplied any password at all. Is that a safe way? Also, It's working for more than 2 databases on same server. However, now when I try to connect from another server, I'm getting below error: ERROR: could not connect to server "postgres_server" DETAIL: fe_sendauth: no password supplied
    – Shiwangini
    Nov 26, 2019 at 6:57

1 Answer 1

0

"fe_sendauth: no password supplied"

The foreign server is demanding a password (due to pg_hba.conf being set to md5, presumably) and you are not supplying one. - jjanes


I altered my user mapping (from another server) and provided password there and got it working. Like below,

CREATE USER MAPPING FOR PUBLIC SERVER postgres_server OPTIONS 
(user 'postgres' , password 'xxxxxxx');

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.