Re: FDW connections

Thanks guys. I realise it was an odd request. The scenario is I'm building
a mechanism for an application to operate in limited capacity using a
secondary database while the primary database is being upgraded. I'm using
postgres_fdw to sync changes between the primary and secondary databases.
The reason for the question was during my testing I was switching between
'modes' (we refer to them as online and offline), and during the secondary
database setup process, it renames a database if it exists. That was
failing due to an existing connection that ended up being from the primary
database during its 'sync-from-offline' process from the previous test. The
primary database connection still existed because it was made from a
connection pool. So, the bottom line is that this was a somewhat contrived
situation, and I was able to release the connection from the pool after
performing the fdw query.

We're using AWS RDS, so we've had to implement our own 'zero-downtime'
functionality. RDS also means we're a bit behind version-wise. The latest
version we have available today is 12.5, so I imagine it will be quite a
while before PG14 is a possibility.

Thanks very much for your help.

Kind regards,

Steve

On Fri, Jan 22, 2021 at 7:32 PM Hou, Zhijie <houzj(dot)fnst(at)cn(dot)fujitsu(dot)com>
wrote:

> > > If I have made a query on a foreign table (using postgres_fdw), it
> > > establishes a connection automatically. Is there any way to
> > > disconnect that fdw connection without disconnecting the session that
> > > instigated it?
> >
> > No.
> >
> > From PostgreSQL v14 on, there is the "idle_session_timeout" that you
> could
> > set on the server to close such sessions. postgresql_fdw will silently
> > re-establish such broken connections. You could set this parameter in
> the
> > foreign server definition.
> >
> > But this is a strange request: why would you want to close such
> connections
> > before the database session ends?
> >
>
> Hi
>
> There are two new functions being reviewed called:
>
> postgres_fdw_disconnect()
> postgres_fdw_disconnect_all()
>
> These function may solve your problem,
> If you are interested in that, you can take a look at [1].
>
> The functions have not been committed yet, it may can be used in PG14.
>
> [1]
> https://www.postgresql.org/message-id/CALj2ACVcpU%3DwB7G%3DzT8msVHvPs0-y0BbviupiT%2Bf3--bGYaOMA%40mail.gmail.com
>
>
> Best regards,
> houzj
>
>
>
>
>
>
>
>