Обсуждение: pg_ctl stop failure
8.4.13 & PostGIS 2.0 on Red Hat 6 I want to do a offline backup of /var/lib/pgsql/data But whenever I do Pg_ctl stop I get -bash-4.1$ pg_ctl stop waiting for server to shut down............................................................... failed pg_ctl: server does not shut down 1. If I do pg_ctl stop -m f the cluster and databases shutdown as required but is this a clean and/or consistent shutdown of the cluster in the sensethat I could recover from the OS cp -r /var/lib/pgsql/data command that I did? 2. Any ideas as to what could be causing the pg_ctl stop command to fail. I tried doing it again after a -m f stop followedby a pg_ctl start but still no good. Thanks Austen Birchall DBA Met Office
On Fri, May 17, 2013 at 10:47 PM, Birchall, Austen <austen.birchall@metoffice.gov.uk> wrote: > 8.4.13 & PostGIS 2.0 on Red Hat 6 > > I want to do a offline backup of /var/lib/pgsql/data > > But whenever I do > > Pg_ctl stop > > I get > > -bash-4.1$ pg_ctl stop > waiting for server to shut down............................................................... failed > pg_ctl: server does not shut down > This means you have clients connected to the database and server won't shut down until all the clients have disconnected. > 1. If I do > > pg_ctl stop -m f > > the cluster and databases shutdown as required but is this a clean and/or consistent shutdown of the cluster in the sensethat I could recover from the OS > "-m f " mode disconnects those clients for you. pg_ctl Documentation at http://www.postgresql.org/docs/8.4/static/app-pg-ctl.html says: "Fast" mode does not wait for clients to disconnect and will terminate an online backup in progress. All active transactions are rolled back and clients are forcibly disconnected, then the server is shut down. So what you have is a consistent shut down with incomplete transactions rolled back. By the way, could you see any active connections while you tried stopping the server? -- Amit Langote
Thanks for this - to be honest I didn't check but if I look at it now I get some transactions with | 2013-05-17 15:08:25.973161+00 | <IDLE> in transaction | 2013-05-17 15:08:25.745154+00 | <IDLE> in transaction | 2013-05-17 14:58:24.066386+00 | <IDLE> in transaction | 2013-05-17 14:58:24.224678+00 | <IDLE> in transaction Which I suppose I could kill using pg_terminate_backend() although if they are rollbacked anyway? When I do pg_ctl start does PostgreSQL attempt to re-write them (from the WAL logs?) or as I suspect are they gone for good? Austen -----Original Message----- From: Amit Langote [mailto:amitlangote09@gmail.com] Sent: 17 May 2013 16:00 To: Birchall, Austen Cc: pgsql-novice@postgresql.org Subject: Re: [NOVICE] pg_ctl stop failure On Fri, May 17, 2013 at 10:47 PM, Birchall, Austen <austen.birchall@metoffice.gov.uk> wrote: > 8.4.13 & PostGIS 2.0 on Red Hat 6 > > I want to do a offline backup of /var/lib/pgsql/data > > But whenever I do > > Pg_ctl stop > > I get > > -bash-4.1$ pg_ctl stop > waiting for server to shut > down............................................................... > failed > pg_ctl: server does not shut down > This means you have clients connected to the database and server won't shut down until all the clients have disconnected. > 1. If I do > > pg_ctl stop -m f > > the cluster and databases shutdown as required but is this a clean > and/or consistent shutdown of the cluster in the sense that I could > recover from the OS > "-m f " mode disconnects those clients for you. pg_ctl Documentation at http://www.postgresql.org/docs/8.4/static/app-pg-ctl.html says: "Fast" mode does not wait for clients to disconnect and will terminate an online backup in progress. All active transactionsare rolled back and clients are forcibly disconnected, then the server is shut down. So what you have is a consistent shut down with incomplete transactions rolled back. By the way, could you see any activeconnections while you tried stopping the server? -- Amit Langote
On Sat, May 18, 2013 at 12:21 AM, Birchall, Austen <austen.birchall@metoffice.gov.uk> wrote: > Thanks for this - to be honest I didn't check but if I look at it now I get some transactions with > > | 2013-05-17 15:08:25.973161+00 | <IDLE> in transaction > | 2013-05-17 15:08:25.745154+00 | <IDLE> in transaction > | 2013-05-17 14:58:24.066386+00 | <IDLE> in transaction > | 2013-05-17 14:58:24.224678+00 | <IDLE> in transaction > > Which I suppose I could kill using pg_terminate_backend() although if they are rollbacked anyway? > > > When I do pg_ctl start does PostgreSQL attempt to re-write them (from the WAL logs?) or as I suspect are they gone forgood? > When you did "pg_ctl -D <data-dir> -m f stop", they were gone. I reckon you did that already, right? Yes, since they are rolled back, they are gone for good. What did you say about after starting the server back with "pg_ctl -D <data-dir> start"? -- Amit Langote
It is not necessary to shutdown the database to take a filesystem level backup. you might consider to use pg_start_backup() cp ... pg_stop_backup() Take a look at the documentation for further details http://www.postgresql.org/docs/8.4/static/functions-admin.html regards Andreas On 05/17/2013 05:00 PM, Amit Langote wrote: > On Fri, May 17, 2013 at 10:47 PM, Birchall, Austen > <austen.birchall@metoffice.gov.uk> wrote: >> 8.4.13 & PostGIS 2.0 on Red Hat 6 >> >> I want to do a offline backup of /var/lib/pgsql/data >> >> But whenever I do >> >> Pg_ctl stop >> >> I get >> >> -bash-4.1$ pg_ctl stop >> waiting for server to shut down............................................................... failed >> pg_ctl: server does not shut down >> > This means you have clients connected to the database and server won't > shut down until all the clients have disconnected. > >> 1. If I do >> >> pg_ctl stop -m f >> >> the cluster and databases shutdown as required but is this a clean and/or consistent shutdown of the cluster in the sensethat I could recover from the OS >> > "-m f " mode disconnects those clients for you. pg_ctl Documentation > at http://www.postgresql.org/docs/8.4/static/app-pg-ctl.html > says: > "Fast" mode does not wait for clients to disconnect and will terminate > an online backup in progress. All active transactions are rolled back > and clients are forcibly disconnected, then the server is shut down. > > So what you have is a consistent shut down with incomplete > transactions rolled back. By the way, could you see any active > connections while you tried stopping the server? > > -- > Amit Langote > >
Amit What I actually did was a pg_ctl stop -m f followed by a pg_ctl start then tried a 'simple' pg_stop stop but this hung again so there must have been 'new' connections coming in. Austen -----Original Message----- From: Amit Langote [mailto:amitlangote09@gmail.com] Sent: 17 May 2013 16:37 To: Birchall, Austen Cc: pgsql-novice@postgresql.org Subject: Re: [NOVICE] pg_ctl stop failure On Sat, May 18, 2013 at 12:21 AM, Birchall, Austen <austen.birchall@metoffice.gov.uk> wrote: > Thanks for this - to be honest I didn't check but if I look at it now > I get some transactions with > > | 2013-05-17 15:08:25.973161+00 | <IDLE> in transaction > | 2013-05-17 15:08:25.745154+00 | <IDLE> in transaction > | 2013-05-17 14:58:24.066386+00 | <IDLE> in transaction > | 2013-05-17 14:58:24.224678+00 | <IDLE> in transaction > > Which I suppose I could kill using pg_terminate_backend() although if they are rollbacked anyway? > > > When I do pg_ctl start does PostgreSQL attempt to re-write them (from the WAL logs?) or as I suspect are they gone forgood? > When you did "pg_ctl -D <data-dir> -m f stop", they were gone. I reckon you did that already, right? Yes, since they are rolled back, they are gone for good. What did you say about after starting the server back with "pg_ctl -D <data-dir> start"? -- Amit Langote
Hi Andreas Due to permissions associated with the ESRI 'system' user even in 9.* you can't do a simple restore from an online backup- you have to restore public first- for 8.3 I have unable to get this solution to work hence going down the offlineroute, which is actually not an issue as regards downtime for the particular databases I am working on at present. Austen -----Original Message----- From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org] Sent: 19 May 2013 10:20 To: pgsql-novice@postgresql.org Subject: [pgsql-novice] Daily digest v1.3342 (1 messages) Message Digest Volume 1 : Issue 3342 : "mime" Format Messages in this Issue: Re: pg_ctl stop failure
There is no need for a restore. The online copy is a snapshot of the cluster dir and can be used like an offline copy. The advantage is that no databases and backend processes need to be terminated. regards Andreas On 05/20/2013 11:57 AM, Birchall, Austen wrote: > Hi Andreas > > Due to permissions associated with the ESRI 'system' user even in 9.* you can't do a simple restore from an online backup- you have to restore public first- for 8.3 I have unable to get this solution to work hence going down the offlineroute, which is actually not an issue as regards downtime for the particular databases I am working on at present. > > Austen > > -----Original Message----- > From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org] > Sent: 19 May 2013 10:20 > To: pgsql-novice@postgresql.org > Subject: [pgsql-novice] Daily digest v1.3342 (1 messages) > > Message Digest > Volume 1 : Issue 3342 : "mime" Format > > Messages in this Issue: > Re: pg_ctl stop failure > >
As show archive_command; archive_command ----------------- (disabled) (1 row) Doesn't this mean that I can't use pg_start_backup but something to keep in mind for the future Thanks Austen -----Original Message----- From: Andreas Schmitz [mailto:mailinglist@longimanus.net] Sent: 21 May 2013 16:45 To: Birchall, Austen Cc: pgsql-novice@postgresql.org Subject: Re: [NOVICE] pg_ctl stop failure There is no need for a restore. The online copy is a snapshot of the cluster dir and can be used like an offline copy. Theadvantage is that no databases and backend processes need to be terminated. regards Andreas On 05/20/2013 11:57 AM, Birchall, Austen wrote: > Hi Andreas > > Due to permissions associated with the ESRI 'system' user even in 9.* you can't do a simple restore from an online backup- you have to restore public first- for 8.3 I have unable to get this solution to work hence going down the offlineroute, which is actually not an issue as regards downtime for the particular databases I am working on at present. > > Austen > > -----Original Message----- > From: pgsql-novice-owner@postgresql.org > [mailto:pgsql-novice-owner@postgresql.org] > Sent: 19 May 2013 10:20 > To: pgsql-novice@postgresql.org > Subject: [pgsql-novice] Daily digest v1.3342 (1 messages) > > Message Digest > Volume 1 : Issue 3342 : "mime" Format > > Messages in this Issue: > Re: pg_ctl stop failure > >
You have to set the wal_level to archive in postgresql.conf regards Andreas On 05/22/2013 12:31 PM, Birchall, Austen wrote: > As > > show archive_command; > archive_command > ----------------- > (disabled) > (1 row) > > Doesn't this mean that I can't use pg_start_backup but something to keep in mind for the future > > Thanks > > Austen > > > -----Original Message----- > From: Andreas Schmitz [mailto:mailinglist@longimanus.net] > Sent: 21 May 2013 16:45 > To: Birchall, Austen > Cc: pgsql-novice@postgresql.org > Subject: Re: [NOVICE] pg_ctl stop failure > > There is no need for a restore. The online copy is a snapshot of the cluster dir and can be used like an offline copy.The advantage is that no databases and backend processes need to be terminated. > > regards > > Andreas > > > On 05/20/2013 11:57 AM, Birchall, Austen wrote: >> Hi Andreas >> >> Due to permissions associated with the ESRI 'system' user even in 9.* you can't do a simple restore from an online backup- you have to restore public first- for 8.3 I have unable to get this solution to work hence going down the offlineroute, which is actually not an issue as regards downtime for the particular databases I am working on at present. >> >> Austen >> >> -----Original Message----- >> From: pgsql-novice-owner@postgresql.org >> [mailto:pgsql-novice-owner@postgresql.org] >> Sent: 19 May 2013 10:20 >> To: pgsql-novice@postgresql.org >> Subject: [pgsql-novice] Daily digest v1.3342 (1 messages) >> >> Message Digest >> Volume 1 : Issue 3342 : "mime" Format >> >> Messages in this Issue: >> Re: pg_ctl stop failure >> >>
And the archive_mode has to be set to on specifying an archive_command in postgresql.conf refer to http://www.postgresql.org/docs/8.3/static/continuous-archiving.html On 05/22/2013 12:31 PM, Birchall, Austen wrote: > As > > show archive_command; > archive_command > ----------------- > (disabled) > (1 row) > > Doesn't this mean that I can't use pg_start_backup but something to keep in mind for the future > > Thanks > > Austen > > > -----Original Message----- > From: Andreas Schmitz [mailto:mailinglist@longimanus.net] > Sent: 21 May 2013 16:45 > To: Birchall, Austen > Cc: pgsql-novice@postgresql.org > Subject: Re: [NOVICE] pg_ctl stop failure > > There is no need for a restore. The online copy is a snapshot of the cluster dir and can be used like an offline copy.The advantage is that no databases and backend processes need to be terminated. > > regards > > Andreas > > > On 05/20/2013 11:57 AM, Birchall, Austen wrote: >> Hi Andreas >> >> Due to permissions associated with the ESRI 'system' user even in 9.* you can't do a simple restore from an online backup- you have to restore public first- for 8.3 I have unable to get this solution to work hence going down the offlineroute, which is actually not an issue as regards downtime for the particular databases I am working on at present. >> >> Austen >> >> -----Original Message----- >> From: pgsql-novice-owner@postgresql.org >> [mailto:pgsql-novice-owner@postgresql.org] >> Sent: 19 May 2013 10:20 >> To: pgsql-novice@postgresql.org >> Subject: [pgsql-novice] Daily digest v1.3342 (1 messages) >> >> Message Digest >> Volume 1 : Issue 3342 : "mime" Format >> >> Messages in this Issue: >> Re: pg_ctl stop failure >> >>
Actually Apart from semi-regular bulb data loads these databases are pretty much static so I decided not to go into archiving WALmode early on. Thanks for your help though, as I said perhaps something for the future Austen -----Original Message----- From: Andreas Schmitz [mailto:mailinglist@longimanus.net] Sent: 22 May 2013 18:20 To: Birchall, Austen Cc: pgsql-novice@postgresql.org Subject: Re: [NOVICE] pg_ctl stop failure And the archive_mode has to be set to on specifying an archive_command in postgresql.conf refer to http://www.postgresql.org/docs/8.3/static/continuous-archiving.html On 05/22/2013 12:31 PM, Birchall, Austen wrote: > As > > show archive_command; > archive_command > ----------------- > (disabled) > (1 row) > > Doesn't this mean that I can't use pg_start_backup but something to > keep in mind for the future > > Thanks > > Austen > > > -----Original Message----- > From: Andreas Schmitz [mailto:mailinglist@longimanus.net] > Sent: 21 May 2013 16:45 > To: Birchall, Austen > Cc: pgsql-novice@postgresql.org > Subject: Re: [NOVICE] pg_ctl stop failure > > There is no need for a restore. The online copy is a snapshot of the cluster dir and can be used like an offline copy.The advantage is that no databases and backend processes need to be terminated. > > regards > > Andreas > > > On 05/20/2013 11:57 AM, Birchall, Austen wrote: >> Hi Andreas >> >> Due to permissions associated with the ESRI 'system' user even in 9.* you can't do a simple restore from an online backup- you have to restore public first- for 8.3 I have unable to get this solution to work hence going down the offlineroute, which is actually not an issue as regards downtime for the particular databases I am working on at present. >> >> Austen >> >> -----Original Message----- >> From: pgsql-novice-owner@postgresql.org >> [mailto:pgsql-novice-owner@postgresql.org] >> Sent: 19 May 2013 10:20 >> To: pgsql-novice@postgresql.org >> Subject: [pgsql-novice] Daily digest v1.3342 (1 messages) >> >> Message Digest >> Volume 1 : Issue 3342 : "mime" Format >> >> Messages in this Issue: >> Re: pg_ctl stop failure >> >>
We have some 'big' and some 'small' tablespaces that we what to monitor using pg_tablespace_size Now we know that we can use pg_size_pretty to tidy up the output but we really need the output to be in either GB or MBconsistently regardless of the actual size of the tablespace. Any ways to do this? Thanks Austen
pg_size_pretty does not take any arguments. You could write your own procedure to normalize the output. regards Andreas On 05/29/2013 04:33 PM, Birchall, Austen wrote: > We have some 'big' and some 'small' tablespaces that we what to monitor using pg_tablespace_size > > Now we know that we can use pg_size_pretty to tidy up the output but we really need the output to be in either GB or MBconsistently regardless of the actual size of the tablespace. > > Any ways to do this? > > Thanks > > Austen > >
I guessed that this is what we would have to do but it was worth a quick question just in case Apologies for forgetting to put a title on this one Regards Austen -----Original Message----- From: Birchall, Austen Sent: 29 May 2013 15:33 To: pgsql-novice@postgresql.org Subject: We have some 'big' and some 'small' tablespaces that we what to monitor using pg_tablespace_size Now we know that we can use pg_size_pretty to tidy up the output but we really need the output to be in either GB or MBconsistently regardless of the actual size of the tablespace. Any ways to do this? Thanks Austen
9.1.9 on Red Hat 6 64 bit Hi I have set postgresql.conf:listen_addresses = '*' # what IP address(es) to listen on; But even after a number of stops & restarts I still get show listen_addresses; listen_addresses ------------------ localhost (1 row) Any ideas why the change is not being auctioned? Thanks Austen Austen Birchall DBA Met Office
I just spotted that my server seems to have 2 postgresql.conf files installed One at /var/lib/pgsql/data/postgresql.conf And another one at /var/lib/pgsql/9.1/data/postgresql.conf By convention which is the correct one to use? Austen 9.1.9 on Red Hat 6 64 bit Hi I have set postgresql.conf:listen_addresses = '*' # what IP address(es) to listen on; But even after a number of stops & restarts I still get show listen_addresses; listen_addresses ------------------ localhost (1 row) Any ideas why the change is not being auctioned? Thanks Austen Austen Birchall DBA Met Office
Birchall,
Check which config your server is using by running : select setting from pg_settings where name='config_file' and update listen_address value in that config. I bet you are updating wrong config.Thanks,
Prashanth Goriparthi
Thanking you,
Prashanth Goriparthi
Prashanth Goriparthi
On Tue, Jul 23, 2013 at 10:55 AM, Birchall, Austen <austen.birchall@metoffice.gov.uk> wrote:
I just spotted that my server seems to have 2 postgresql.conf files installed
One at
/var/lib/pgsql/data/postgresql.conf
And another one at
/var/lib/pgsql/9.1/data/postgresql.conf
By convention which is the correct one to use?
Austen
9.1.9 on Red Hat 6 64 bit
Hi
I have set
postgresql.conf:listen_addresses = '*' # what IP address(es) to listen on;
But even after a number of stops & restarts I still get
show listen_addresses;
listen_addresses
------------------
localhost
(1 row)
Any ideas why the change is not being auctioned?
Thanks
Austen
Austen Birchall
DBA
Met Office
--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice
On Tue, Jul 23, 2013 at 6:55 PM, Birchall, Austen <austen.birchall@metoffice.gov.uk> wrote: > I just spotted that my server seems to have 2 postgresql.conf files installed > > One at > > /var/lib/pgsql/data/postgresql.conf > > And another one at > > /var/lib/pgsql/9.1/data/postgresql.conf > Check that they are effectively two different files and not a link (many distros do this kind of trick). As suggested you can check the value and the settings from the catalog: select name,setting, source, sourcefile, sourceline from pg_settings where name like '%listen%' If you get a null sourcefile it could be that some init script is overriding the configuration somehow. Check for all the running processes and see if postmaster is invoked with the -c option, that will point you to the config file to modify., Luca
On Tue, Jul 23, 2013 at 9:34 PM, Luca Ferrari <fluca1978@infinito.it> wrote: > If you get a null sourcefile it could be that some init script is > overriding the configuration somehow. Check for all the running > processes and see if postmaster is invoked with the -c option, that > will point you to the config file to modify., I meant look for -c config_file Luca
All - many thanks I'm now looking at the 'correct' file locsng=# show listen_addresses; listen_addresses ------------------ * (1 row) locsng=# Austen Austen Birchall Senior Database Administrator Met Office FitzRoy Road Exeter EX1 3PB United Kingdom Tel: +44 (0)1392 884481 Fax: +44 (0)1392 885681 E-mail: austen.birchall@metoffice.gov.uk Website: http://www.metoffice.gov.uk -----Original Message----- From: fluca1978@gmail.com [mailto:fluca1978@gmail.com] On Behalf Of Luca Ferrari Sent: 23 July 2013 20:44 To: Birchall, Austen Cc: pgsql-novice@postgresql.org Subject: Re: [NOVICE] Changing listen_addresses On Tue, Jul 23, 2013 at 9:34 PM, Luca Ferrari <fluca1978@infinito.it> wrote: > If you get a null sourcefile it could be that some init script is > overriding the configuration somehow. Check for all the running > processes and see if postmaster is invoked with the -c option, that > will point you to the config file to modify., I meant look for -c config_file Luca