Postgres Pro
Downloads
Home   >   mailing lists

Thread: Promote replica before being able to accept connections

Promote replica before being able to accept connections

From
Martín Fernández
Date:
Hello Everyone!

We have a 9.2 pg cluster and we are in the process of rebuilding a master database in our staging environment. In order to achieve the latter goal, we are restoring our staging database using pg_basebackup against one of our production replicas. 

pg_basebackup has completed and the staging database is configured in hot_standby mode, recovering from the WAL archive. The database has not reached the point were it can accept connections yet, still investing WAL files.

What we are not sure about is, can we attempt promoting the database as a master instance before it can accept connections ? We don´t care if the staging database is a few days behind the production environment, that is something acceptable in our use case, we just want to promote it as soon as we can.

Thanks in advance!

Best,
Martín

Re: Promote replica before being able to accept connections

From
Laurenz Albe
Date:
Martín Fernández wrote:
> We have a 9.2 pg cluster and we are in the process of rebuilding a master database in our staging environment.
> In order to achieve the latter goal, we are restoring our staging database using pg_basebackup against one
> of our production replicas. 
> 
> pg_basebackup has completed and the staging database is configured in hot_standby mode, recovering from
> the WAL archive. The database has not reached the point were it can accept connections yet, still
> investing WAL files.
> 
> What we are not sure about is, can we attempt promoting the database as a master instance before it can
> accept connections ? We don´t care if the staging database is a few days behind the production environment,
> that is something acceptable in our use case, we just want to promote it as soon as we can.

The earliest time to which you can recover is the end of the backup.

Until recovery has reached that point, the database is not consistent
(that's the "consistent recovery state" the logs are talking about).

You cannot connect to the recovering database or promote it before you
reach that point, so you have to wait until you can connectio to the database
before you can promote it.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: Promote replica before being able to accept connections

From
Martín Fernández
Date:
Laurenz,

That makes sense! 

Thanks for the quick reply

Best,

Martín

On Mon, Feb 11th, 2019 at 7:55 AM, Laurenz Albe <laurenz.albe@cybertec.at> wrote:

Martín Fernández wrote:
> We have a 9.2 pg cluster and we are in the process of rebuilding a master database in our staging environment.
> In order to achieve the latter goal, we are restoring our staging database using pg_basebackup against one
> of our production replicas.
>
> pg_basebackup has completed and the staging database is configured in hot_standby mode, recovering from
> the WAL archive. The database has not reached the point were it can accept connections yet, still
> investing WAL files.
>
> What we are not sure about is, can we attempt promoting the database as a master instance before it can
> accept connections ? We don´t care if the staging database is a few days behind the production environment,
> that is something acceptable in our use case, we just want to promote it as soon as we can.

The earliest time to which you can recover is the end of the backup.

Until recovery has reached that point, the database is not consistent
(that's the "consistent recovery state" the logs are talking about).

You cannot connect to the recovering database or promote it before you
reach that point, so you have to wait until you can connectio to the database
before you can promote it.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com


Re: Promote replica before being able to accept connections

From
Michael Paquier
Date:
On Mon, Feb 11, 2019 at 06:59:27AM -0800, Martín Fernández wrote:
> That makes sense!
>
> Thanks for the quick reply

Note that PostgreSQL 9.4 has introduced a new parameter in
recovery.conf that allows recovery to finish exactly when a consistent
state has been reached:
recovery_target = 'immediate'

So that's basically what you are looking for.  Now you are on 9.2, and
new features are not back-ported.
--
Michael
Attachment