Promote replica before being able to accept connections
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
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
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:
Show quoted text
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
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