pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedUnable to start postgres instance.
5+ messages / 4 participants
[nested] [flat]
* Unable to start postgres instance.
@ 2017-04-27 19:38 Gone, Sajan <SGone@lb.com>
2017-04-27 19:45 ` Re: Unable to start postgres instance. Hunley, Douglas <douglas.hunley@openscg.com>
0 siblings, 1 reply; 5+ messages in thread
From: Gone, Sajan @ 2017-04-27 19:38 UTC (permalink / raw)
To: pgsql-admin
Hi,
I am in the process of setting postgres replication and I have followed the documentation from the website below.
https://www.digitalocean.com/community/tutorials/how-to-set-up-master-slave-replication-on-postgresq...
I have completed all the steps and when I was about to start the slave server, seems like it got hung up on the start up and is taking forever to be available. Here is the process list I am seeing.
-bash-4.3$ ps -ef | grep -i postgres
postgres 29842 29841 0 14:01:45 pts/3 0:00 -bash
postgres 42808 42806 0 15:17:19 ? 0:00 /opt/local/bin/postgres -D /var/pgsql/data
postgres 45713 31142 0 15:35:45 pts/2 0:00 ps -ef
root 31141 29120 0 14:11:05 pts/2 0:00 sudo -u postgres -i
postgres 42806 6491 0 15:17:19 pts/2 0:00 /opt/local/bin/postgres -D /var/pgsql/data
postgres 31142 31141 0 14:11:05 pts/2 0:00 -bash
postgres 45712 42806 0 15:35:44 ? 0:00 /opt/local/bin/postgres -D /var/pgsql/data
root 29841 29835 0 14:01:43 pts/3 0:00 sudo -u postgres -i
postgres 45714 31142 0 15:35:45 pts/2 0:00 grep -i postgres
postgres 42807 42806 0 15:17:19 ? 0:00 /opt/local/bin/postgres -D /var/pgsql/data
Below is the error I am seeing when I am trying to log on to the DB using psql client.
-bash-4.3$ psql -U postgres
psql: FATAL: the database system is starting up
However its been on that from quite a long time. It would really appreciate if anyone could explain me on what is really going on.
Thank You,
Sajan Gone
Database Administrator
L Brands
Mobile #: 517-990-5282
Office #: 614-577-7622
________________________________
Notice: This communication may contain privileged and/or confidential information. If you are not the intended recipient, please notify the sender by email, and immediately delete the message and any attachments without copying or disclosing them. LB may, for any reason, intercept, access, use, and disclose any information that is communicated by or through, or which is stored on, its networks, applications, services, and devices.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Unable to start postgres instance.
2017-04-27 19:38 Unable to start postgres instance. Gone, Sajan <SGone@lb.com>
@ 2017-04-27 19:45 ` Hunley, Douglas <douglas.hunley@openscg.com>
2017-04-27 21:43 ` Re: Unable to start postgres instance. Gone, Sajan <SGone@lb.com>
0 siblings, 1 reply; 5+ messages in thread
From: Hunley, Douglas @ 2017-04-27 19:45 UTC (permalink / raw)
To: Gone, Sajan <SGone@lb.com>; +Cc: pgsql-admin
On Thu, Apr 27, 2017 at 3:38 PM, Gone, Sajan <SGone@lb.com> wrote:
> -bash-4.3$ psql -U postgres
> psql: FATAL: the database system is starting up
>
> However its been on that from quite a long time. It would really
> appreciate if anyone could explain me on what is really going on.
>
It is doing exactly what it should. A standby is, by default, in permanent
recovery mode. If you wish to be able to connect to it in this mode, you
need to add 'hot_standby = on' to its postgresql.conf and restart the
standby.
--
{
"name" : "douglas j hunley",
"title" : "database engineer",
"email" : "douglas.hunley@openscg.com <doug.hunley@gmail.com>",
"mobile" : "+1 614 316 5079"
}
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Unable to start postgres instance.
2017-04-27 19:38 Unable to start postgres instance. Gone, Sajan <SGone@lb.com>
2017-04-27 19:45 ` Re: Unable to start postgres instance. Hunley, Douglas <douglas.hunley@openscg.com>
@ 2017-04-27 21:43 ` Gone, Sajan <SGone@lb.com>
2017-04-27 21:50 ` Re: Unable to start postgres instance. Andrew Foster <afoster@educationalmeasures.com>
0 siblings, 1 reply; 5+ messages in thread
From: Gone, Sajan @ 2017-04-27 21:43 UTC (permalink / raw)
To: Hunley, Douglas <douglas.hunley@openscg.com>; +Cc: pgsql-admin
Hi Douglas,
Appreciate your help.
'hot_standby = on' set up was already made but I restarted the server just in case, however I am still getting the same error.
But when I am attempting to reboot the database this is what I have noticed on the error logs
LOG: started streaming WAL from primary at 0/1B000000 on timeline 1
FATAL: could not receive data from WAL stream: ERROR: requested WAL segment 00000001000000000000001B has already been removed
LOG: received fast shutdown request
LOG: shutting down
LOG: database system is shut down
So, I am assuming the WAL files have already been deleted on the Master. Is there any setting on Master I can toggle in order to increase the expiry(retention policy) for the WAL files.
Thank You,
Sajan Gone
Database Administrator
L Brands
Mobile #: 517-990-5282
Office #: 614-577-7622
From: "Hunley, Douglas" <douglas.hunley@openscg.com<mailto:douglas.hunley@openscg.com>>
Date: Thursday, April 27, 2017 at 3:45 PM
To: Sajan Gone <sgone@lb.com<mailto:sgone@lb.com>>
Cc: "pgsql-admin@postgresql.org<mailto:pgsql-admin@postgresql.org>" <pgsql-admin@postgresql.org<mailto:pgsql-admin@postgresql.org>>
Subject: Re: [ADMIN] Unable to start postgres instance.
On Thu, Apr 27, 2017 at 3:38 PM, Gone, Sajan <SGone@lb.com<mailto:SGone@lb.com>> wrote:
-bash-4.3$ psql -U postgres
psql: FATAL: the database system is starting up
However its been on that from quite a long time. It would really appreciate if anyone could explain me on what is really going on.
It is doing exactly what it should. A standby is, by default, in permanent recovery mode. If you wish to be able to connect to it in this mode, you need to add 'hot_standby = on' to its postgresql.conf and restart the standby.
--
{
"name" : "douglas j hunley",
"title" : "database engineer",
"email" : "douglas.hunley@openscg.com<mailto:doug.hunley@gmail.com>",
"mobile" : "+1 614 316 5079"
}
________________________________
Notice: This communication may contain privileged and/or confidential information. If you are not the intended recipient, please notify the sender by email, and immediately delete the message and any attachments without copying or disclosing them. LB may, for any reason, intercept, access, use, and disclose any information that is communicated by or through, or which is stored on, its networks, applications, services, and devices.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Unable to start postgres instance.
2017-04-27 19:38 Unable to start postgres instance. Gone, Sajan <SGone@lb.com>
2017-04-27 19:45 ` Re: Unable to start postgres instance. Hunley, Douglas <douglas.hunley@openscg.com>
2017-04-27 21:43 ` Re: Unable to start postgres instance. Gone, Sajan <SGone@lb.com>
@ 2017-04-27 21:50 ` Andrew Foster <afoster@educationalmeasures.com>
2017-04-27 22:02 ` Re: Unable to start postgres instance. Cachique <cachique@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Andrew Foster @ 2017-04-27 21:50 UTC (permalink / raw)
To: Gone, Sajan <SGone@lb.com>; +Cc: Hunley, Douglas <douglas.hunley@openscg.com>; pgsql-admin
Afternoon Sajan,
I would try setting your wal_keep_segments to a higher value on the master server. Depending on available HDD space on the master, you should be able to keep a few dozen without any issues. Also, here is a link to a resource that helped me with replication and seems to be a bit better than the one you included upthread. https://wiki.postgresql.org/wiki/Streaming_Replication <https://wiki.postgresql.org/wiki/Streaming_Replication;
Best,
Andrew Foster | Educational Measures, LLC
Database Administrator
7100 E. Belleview Ave, Suite 350
Greenwood Village, CO 80111
303-548-6516
afoster@educationalmeasures.com
> On Apr 27, 2017, at 3:43 PM, Gone, Sajan <SGone@lb.com> wrote:
>
> Hi Douglas,
>
> Appreciate your help.
>
> 'hot_standby = on' set up was already made but I restarted the server just in case, however I am still getting the same error.
>
> But when I am attempting to reboot the database this is what I have noticed on the error logs
>
> LOG: started streaming WAL from primary at 0/1B000000 on timeline 1
> FATAL: could not receive data from WAL stream: ERROR: requested WAL segment 00000001000000000000001B has already been removed
>
> LOG: received fast shutdown request
> LOG: shutting down
> LOG: database system is shut down
>
> So, I am assuming the WAL files have already been deleted on the Master. Is there any setting on Master I can toggle in order to increase the expiry(retention policy) for the WAL files.
>
> Thank You,
> Sajan Gone
> Database Administrator
> L Brands
> Mobile #: 517-990-5282
> Office #: 614-577-7622
> From: "Hunley, Douglas" <douglas.hunley@openscg.com <mailto:douglas.hunley@openscg.com>>
> Date: Thursday, April 27, 2017 at 3:45 PM
> To: Sajan Gone <sgone@lb.com <mailto:sgone@lb.com>>
> Cc: "pgsql-admin@postgresql.org <mailto:pgsql-admin@postgresql.org>" <pgsql-admin@postgresql.org <mailto:pgsql-admin@postgresql.org>>
> Subject: Re: [ADMIN] Unable to start postgres instance.
>
>
> On Thu, Apr 27, 2017 at 3:38 PM, Gone, Sajan <SGone@lb.com <mailto:SGone@lb.com>> wrote:
> -bash-4.3$ psql -U postgres
> psql: FATAL: the database system is starting up
>
> However its been on that from quite a long time. It would really appreciate if anyone could explain me on what is really going on.
>
> It is doing exactly what it should. A standby is, by default, in permanent recovery mode. If you wish to be able to connect to it in this mode, you need to add 'hot_standby = on' to its postgresql.conf and restart the standby.
>
>
> --
> {
> "name" : "douglas j hunley",
> "title" : "database engineer",
> "email" : "douglas.hunley@openscg.com <mailto:doug.hunley@gmail.com>",
> "mobile" : "+1 614 316 5079"
> }
>
>
> Notice: This communication may contain privileged and/or confidential information. If you are not the intended recipient, please notify the sender by email, and immediately delete the message and any attachments without copying or disclosing them. LB may, for any reason, intercept, access, use, and disclose any information that is communicated by or through, or which is stored on, its networks, applications, services, and devices.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Unable to start postgres instance.
2017-04-27 19:38 Unable to start postgres instance. Gone, Sajan <SGone@lb.com>
2017-04-27 19:45 ` Re: Unable to start postgres instance. Hunley, Douglas <douglas.hunley@openscg.com>
2017-04-27 21:43 ` Re: Unable to start postgres instance. Gone, Sajan <SGone@lb.com>
2017-04-27 21:50 ` Re: Unable to start postgres instance. Andrew Foster <afoster@educationalmeasures.com>
@ 2017-04-27 22:02 ` Cachique <cachique@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Cachique @ 2017-04-27 22:02 UTC (permalink / raw)
To: Andrew Foster <afoster@educationalmeasures.com>; +Cc: Gone, Sajan <SGone@lb.com>; Hunley, Douglas <douglas.hunley@openscg.com>; pgsql-admin
Hi Sajan.
Two things.
First I'd recommend this tutorial
http://www.rassoc.com/gregr/weblog/2013/02/16/zero-to-postgresql-streaming-replication-in-10-mins/
Or any tutorial which uses the more advanced pg_basebackup instead of
pg_start_backup and pg_stop_backup. There is an option for pg_basebackup
which ensures all required WAL files are copied during the initial process
of replication.
And second I'd go for Replications Slots on the master to prevent deletion
of required WAL files during normal replication.
Care to be taken not to fill your disk space with WAL due to disconnected
replicas.
Regards,
Walter
On Thu, Apr 27, 2017 at 5:50 PM, Andrew Foster <
afoster@educationalmeasures.com> wrote:
> Afternoon Sajan,
>
> I would try setting your wal_keep_segments to a higher value on the master
> server. Depending on available HDD space on the master, you should be able
> to keep a few dozen without any issues. Also, here is a link to a resource
> that helped me with replication and seems to be a bit better than the one
> you included upthread. https://wiki.postgresql.org/wiki/Streaming_
> Replication
>
>
> Best,
>
> Andrew Foster | Educational Measures, LLC
> Database Administrator
> 7100 E. Belleview Ave, Suite 350
> Greenwood Village, CO 80111
> 303-548-6516
> afoster@educationalmeasures.com
>
> On Apr 27, 2017, at 3:43 PM, Gone, Sajan <SGone@lb.com> wrote:
>
> Hi Douglas,
>
> Appreciate your help.
>
> 'hot_standby = on' set up was already made but I restarted the server just
> in case, however I am still getting the same error.
>
> But when I am attempting to reboot the database this is what I have
> noticed on the error logs
>
> LOG: started streaming WAL from primary at 0/1B000000 on timeline 1
> FATAL: could not receive data from WAL stream: ERROR: requested WAL
> segment 00000001000000000000001B has already been removed
>
> LOG: received fast shutdown request
> LOG: shutting down
> LOG: database system is shut down
>
> So, I am assuming the WAL files have already been deleted on the Master.
> Is there any setting on Master I can toggle in order to increase the
> expiry(retention policy) for the WAL files.
>
> Thank You,
> Sajan Gone
> Database Administrator
> L Brands
> Mobile #: 517-990-5282
> Office #: 614-577-7622
> From: "Hunley, Douglas" <douglas.hunley@openscg.com>
> Date: Thursday, April 27, 2017 at 3:45 PM
> To: Sajan Gone <sgone@lb.com>
> Cc: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
> Subject: Re: [ADMIN] Unable to start postgres instance.
>
>
> On Thu, Apr 27, 2017 at 3:38 PM, Gone, Sajan <SGone@lb.com> wrote:
>
>> -bash-4.3$ psql -U postgres
>> psql: FATAL: the database system is starting up
>>
>> However its been on that from quite a long time. It would really
>> appreciate if anyone could explain me on what is really going on.
>>
>
> It is doing exactly what it should. A standby is, by default, in permanent
> recovery mode. If you wish to be able to connect to it in this mode, you
> need to add 'hot_standby = on' to its postgresql.conf and restart the
> standby.
>
>
> --
> {
> "name" : "douglas j hunley",
> "title" : "database engineer",
> "email" : "douglas.hunley@openscg.com <doug.hunley@gmail.com>",
> "mobile" : "+1 614 316 5079 <+1%20614-316-5079>"
> }
>
> ------------------------------
>
> Notice: This communication may contain privileged and/or confidential
> information. If you are not the intended recipient, please notify the
> sender by email, and immediately delete the message and any attachments
> without copying or disclosing them. LB may, for any reason, intercept,
> access, use, and disclose any information that is communicated by or
> through, or which is stored on, its networks, applications, services, and
> devices.
>
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2017-04-27 22:02 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-04-27 19:38 Unable to start postgres instance. Gone, Sajan <SGone@lb.com>
2017-04-27 19:45 ` Hunley, Douglas <douglas.hunley@openscg.com>
2017-04-27 21:43 ` Gone, Sajan <SGone@lb.com>
2017-04-27 21:50 ` Andrew Foster <afoster@educationalmeasures.com>
2017-04-27 22:02 ` Cachique <cachique@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox