Re: Return codes for archive and restore commands

[Michael Paquier]

On Wed, Nov 28, 2018 at 10:27:31PM -0500, Stephen Frost wrote:
> Yes, it couldn't be exactly the same as a generic background worker,
> that's a good point.  We definitely need to make sure that the
> postmaster waits for the archiver to shut down, as it does for the WAL
> senders.

Just to be clear, please note I don't think that what removing the
archiver code from the core code is a bad idea, quite the contrary
actually.  But I doubt that it would be acceptable to rip off this code
without something which has the same properties and guarantees for any
users depending on it.  And archive_command is used a lot.
--
Michael


signature.asc
Description: PGP signature

Updating the intro for packages - improve usability, reduce new user confusion

[Craig Ringer]

TL;DR: It's time to update the docs to make package-based and
installer-based PostgreSQL the assumed environment our users are
working with. Or at least put it on an equal footing as a first-class
citizen, not relegate it to the dark corners of notes and appendices.
The same is true for client drivers.

I'll put my hand up to start the work, with a gradual series of small
incremental patches, but only if I can get broad agreement here that
it's the right thing to do first. Otherwise it's just wasting time.

Why?

I see a lot of confusion on Stack Overflow, chat groups, forums etc
that all stems from a common cause:

The PostgreSQL manual, including the intro/tutorial, is written with
the assumption you installed from source and are using pg_ctl, etc
directly. Very few of our users do this.

The reality is that most users are immediately faced with:

psql: FATAL:  role "myname" does not exist

as their first PostgreSQL experience, or

psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

I think it's time to admit that most users first experience PostgreSQL
via packages or installers, and moreover that we should encourage
that. Joe "Never Used Linux Before" Bloggs and Julie "Is Terminal
where I put the commands?" Smith should not be dealing with configure,
make, macports, etc.

We shouldn't leave them in the dark to figure out they want "systemctl
enable --now postgresql-11.service" and that they need to
"/usr/pgsql-10/bin/postgresql-10-setup" first. Or are they on Debian?
Oh, then they need `pg_ctlcluster`, and the cluster is autocreated. We
let the poor confused user sort this mess out.

We, PGDG, ship these packages. We shouldn't entirely ignore them in
our own documentation. That sucks for users. PostgreSQL has a
reputation as a great DBMS that's hard to get started with. That
reputation is deserved. Some trivial changes can ease that learning
curve a lot.

So of course we get people installing Pg from packages, then
initdb'ing and pg_ctl'ing their own clusters instead of using
pg_createcluster or working with systemctl. Sigh. Then they complain
that postgres "crashed" when they restarted, because of course they
didn't integrate it with their init system. How would they? We didn't
tell them how, or that they had to. It's such a mess and I have better
things to do than deal with these unnecessary horrors when I work with
clients on real problems.

Look at what some popular systems do. Slick docs that get any idiot
straight to running the system, even if perhaps they shouldn't. I
don't want to go that far, with the sort of

   curl https://get.postgresql.org/11 | sudo bash

horror that I see many places. But we can do better than we do now
without encouraging hideously insecure and unmaintainable deployments.

(It's bad enough that we nearly totally ignore the client drivers
everyone actually uses - come on, how many of you write direct libpq
code on a day to day basis? Good luck finding PgJDBC, nPgSQL,
psycopg2, DBD::Pg or the Pg gem in our manual; hell, they most don't
even appear to be mentioned in the SOURCE TREE anywhere. psqlODBC is
mentioned - once - in "external projects".).

So I propose to restructure our intro docs to emphasise common package
based deployments, with a focus on the entrypoints at:

* https://www.postgresql.org/docs/current/tutorial-start.html
* https://www.postgresql.org/docs/current/tutorial-install.html
* https://www.postgresql.org/docs/current/installation.html

I realise we cannot cover everything, but we should:

* Move "installation from source code" from "installation.html" to a subhead
* Instead have the "installation" page list:

  - Installation from Packages on Linux
- Fedora, RHEL, CentOS (mention PGDG and distro packages)
- Debian and Ubuntu (mention PGDG and distro packages)
- Other options (-> www.postgresql.org download page)
  - Installation with an installer program
- (-> download page)
   - Installation from Ports on *BSD
   - Installation from source distribution
 - git clone (lets face it, if you use source you use git now)
 - tarball

Keep a section of the manual dedicated to common distribution and
install methods, where we can collect and maintain this info so it's
not scattered. Cross-reference it from elsewhere. So for example under

https://www.postgresql.org/docs/current/server-start.html

we'd xref the distro/OS specific part, and one of those dist/OS
specific parts would cover source installs. Right now when you read
the docs, it's all about the source install, with distro/packaged
stuff a half-considered afterthought mentioned after the manual stuff.
A user should be able to easily open the "Fedora/RHEL/CentOS" section
to get taken to the "Starting PostgreSQL on RHEL, CentOS or Fedora"
section of the distro-specific info in the manual. With, of course, a
note there saying that this applies if you 

Re: First SVG graphic

[Craig Ringer]

On Thu, 29 Nov 2018 at 01:33, Jürgen Purtz  wrote:

> After one week no response at all? Neither positive nor negative. It
> seems that the community has little interest in the SVG issue. Or in my
> suggestion?
>

I'm excited you're doing it. I thought it was part of an existing/ongoing
discussion and didn't really have much to say. I think it's a vital step
forward and we really need to get some graphics into Pg.

Personally I want to let go a bit of the diff-able, merge-able requirements
and accept that not everything plays well when hand written. I got tired of
the arguments that seemed to require that no tool written since 1972 could
be used in our source tree lest SunOS 0.9.ancient have a fit, or someone
find a (gasp) binary in a diff.

So good on you for getting positive action rolling.

-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: Return codes for archive and restore commands

[Stephen Frost]

Greetings,

* Michael Paquier (mich...@paquier.xyz) wrote:
> On Wed, Nov 28, 2018 at 09:39:58PM -0500, Stephen Frost wrote:
> > Having discussed this quite a bit lately with David Steele and Magnus,
> > it's pretty clear that we need to completely rip out how this works
> > today and rewrite it based around an extension model where a background
> > worker can start up and essentially take the place of the archiver
> > process, with flexibility to jump forward through the WAL stream,
> > communicate clearly with other processes, handle failure to do so
> > gracefully based on the specific cases, etc.
> 
> Hm.  When an instance state is in PM_SHUTDOWN_2, the postmaster
> explicitely waits for the WAL senders and the archiver to shut down.  So
> I think that you would need more control regarding the timing a bgworker
> should be shut down first to be completely correct.

Yes, it couldn't be exactly the same as a generic background worker,
that's a good point.  We definitely need to make sure that the
postmaster waits for the archiver to shut down, as it does for the WAL
senders.

Thanks!

Stephen


signature.asc
Description: PGP signature

Re: Return codes for archive and restore commands

[Michael Paquier]

On Wed, Nov 28, 2018 at 09:39:58PM -0500, Stephen Frost wrote:
> Having discussed this quite a bit lately with David Steele and Magnus,
> it's pretty clear that we need to completely rip out how this works
> today and rewrite it based around an extension model where a background
> worker can start up and essentially take the place of the archiver
> process, with flexibility to jump forward through the WAL stream,
> communicate clearly with other processes, handle failure to do so
> gracefully based on the specific cases, etc.

Hm.  When an instance state is in PM_SHUTDOWN_2, the postmaster
explicitely waits for the WAL senders and the archiver to shut down.  So
I think that you would need more control regarding the timing a bgworker
should be shut down first to be completely correct.
--
Michael


signature.asc
Description: PGP signature

Re: Return codes for archive and restore commands

[Stephen Frost]

Greetings,

* Michael Paquier (mich...@paquier.xyz) wrote:
> On Wed, Nov 28, 2018 at 11:00:31AM +, PG Doc comments form wrote:
> > For the archive command:
> > <=128 There are not errors in the PostgreSQL log (messages with severity
> > equal or higher than ERROR). Firstly 3 messages of type LOG about fault,
> > then WARNING about this and pause for 1 minute, then repeated.
> > >=129 FATAL error in the PostgeSQL log. The message about stoping an archive
> > process, but not the database. Repeated after roughly 16 seconds.
> 
> This code is around for some time, and comes from this commit:
> commit: 3ad0728c817bf8abd2c76bd11d856967509b307c
> author: Tom Lane 
> date: Tue, 21 Nov 2006 20:59:53 +
> committer: Tom Lane 
> date: Tue, 21 Nov 2006 20:59:53 +
> On systems that have setsid(2) (which should be just about everything except
> Windows), arrange for each postmaster child process to be its own process
> group leader, and deliver signals SIGINT, SIGTERM, SIGQUIT to the whole
> process group not only the direct child process.  This provides saner behavior
> for archive and recovery scripts; in particular, it's possible to shut down a
> warm-standby recovery server using "pg_ctl stop -m immediate", since delivery
> of SIGQUIT to the startup subprocess will result in killing the waiting
> recovery_command.  Also, this makes Query Cancel and statement_timeout apply
> to scripts being run from backends via system().  (There is no support in the
> core backend for that, but it's widely done using untrusted PLs.)  Per gripe
> from Stephen Harris and subsequent discussion.
> 
> The relevant part if pgarch_archiveXlog() in pgarch.c, and this part
> is most relevant:
> * Per the Single Unix Spec, shells report exit status > 128 when a
> * called command died on a signal.
> 
> > In this case PostgreSQL tries confirm rules for return codes of a unix
> > shell. A unix shell return 126 in the case of "command not executable", 127
> > in the case "command not found", 128+# of signal in the case if application
> > interrupted by uncatched signal.
> 
> If you were to rewrite those paragraphs or make them more precise, how
> would you actually shape your suggestions?  I personally quite like the
> current formulations, but I am rather used to it to be honest.

This is another example, at least imv, of why we really need to move
away from archive_command as an interface for doing WAL archiving.

Having discussed this quite a bit lately with David Steele and Magnus,
it's pretty clear that we need to completely rip out how this works
today and rewrite it based around an extension model where a background
worker can start up and essentially take the place of the archiver
process, with flexibility to jump forward through the WAL stream,
communicate clearly with other processes, handle failure to do so
gracefully based on the specific cases, etc.

We could then possibly write an extension to be included that mimics
what archive_command does today, but imv we should immediately consider
it deprecated and encourage people to move off of it.

Thanks!

Stephen


signature.asc
Description: PGP signature

Re: First SVG graphic

[Tatsuo Ishii]

> On Wed, Nov 28, 2018 at 8:53 PM Alvaro Herrera 
> wrote:
> 
>> On 2018-Nov-28, Bruce Momjian wrote:
>>
>> > On Wed, Nov 28, 2018 at 11:46:33AM -0800, Andres Freund wrote:
>> > > Hi,
>> > >
>> > > On 2018-11-28 18:34:26 +0100, Jürgen Purtz wrote:
>> > > > After one week no response at all? Neither positive nor negative. It
>> seems
>> > > > that the community has little interest in the SVG issue. Or in my
>> > > > suggestion?
>> > >
>> > > I'd suggest describing your proposed workflow in sgml, not a pdf file.
>> >
>> > Well, there were a number of images in the PDF that would be harder to
>> > do in SGML.
>>
>> I think the point is how do you integrate the images from the SVG source
>> into the documentation output.  Presumably that won't be PDF, for
>> example the HTML output will not use a PDF as an image embedded in the
>> page.  It probably works ok for the PDF output (of the whole
>> documentation) to use the PDF of the image ... I suppose the HTML output
>> will need a PNG or such.
>>
>>
> If the source is SVG, why not just use SVG? SVG support in browsers has to
> be pushing 10 years now, shouldn't be a problem at all...  And SVG can be
> embedded in the HTML itself (whether that would work in this particular
> case I don't know, but in theory it can)

+1.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

Re: First SVG graphic

[Magnus Hagander]

On Wed, Nov 28, 2018 at 8:53 PM Alvaro Herrera 
wrote:

> On 2018-Nov-28, Bruce Momjian wrote:
>
> > On Wed, Nov 28, 2018 at 11:46:33AM -0800, Andres Freund wrote:
> > > Hi,
> > >
> > > On 2018-11-28 18:34:26 +0100, Jürgen Purtz wrote:
> > > > After one week no response at all? Neither positive nor negative. It
> seems
> > > > that the community has little interest in the SVG issue. Or in my
> > > > suggestion?
> > >
> > > I'd suggest describing your proposed workflow in sgml, not a pdf file.
> >
> > Well, there were a number of images in the PDF that would be harder to
> > do in SGML.
>
> I think the point is how do you integrate the images from the SVG source
> into the documentation output.  Presumably that won't be PDF, for
> example the HTML output will not use a PDF as an image embedded in the
> page.  It probably works ok for the PDF output (of the whole
> documentation) to use the PDF of the image ... I suppose the HTML output
> will need a PNG or such.
>
>
If the source is SVG, why not just use SVG? SVG support in browsers has to
be pushing 10 years now, shouldn't be a problem at all...  And SVG can be
embedded in the HTML itself (whether that would work in this particular
case I don't know, but in theory it can)

-- 
 Magnus Hagander
 Me: https://www.hagander.net/ 
 Work: https://www.redpill-linpro.com/ 

Re: First SVG graphic

[Andres Freund]

Hi,

On 2018-11-28 14:49:10 -0500, Bruce Momjian wrote:
> On Wed, Nov 28, 2018 at 11:46:33AM -0800, Andres Freund wrote:
> > Hi,
> > 
> > On 2018-11-28 18:34:26 +0100, Jürgen Purtz wrote:
> > > After one week no response at all? Neither positive nor negative. It seems
> > > that the community has little interest in the SVG issue. Or in my
> > > suggestion?
> > 
> > I'd suggest describing your proposed workflow in sgml, not a pdf file.
> 
> Well, there were a number of images in the PDF that would be harder to
> do in SGML.

My point is that that description is going to be needed going forward,
and thus needs to be in a normal doc format. And if the graphics therein
are the first examples for graphics in PG docs, that works for me.

Greetings,

Andres Freund

Re: First SVG graphic

[Bruce Momjian]

On Wed, Nov 28, 2018 at 11:46:33AM -0800, Andres Freund wrote:
> Hi,
> 
> On 2018-11-28 18:34:26 +0100, Jürgen Purtz wrote:
> > After one week no response at all? Neither positive nor negative. It seems
> > that the community has little interest in the SVG issue. Or in my
> > suggestion?
> 
> I'd suggest describing your proposed workflow in sgml, not a pdf file.

Well, there were a number of images in the PDF that would be harder to
do in SGML.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+  Ancient Roman grave inscription +

Re: First SVG graphic

[Bruce Momjian]

On Wed, Nov 28, 2018 at 01:05:28PM -0500, Bruce Momjian wrote:
> On Wed, Nov 28, 2018 at 06:34:26PM +0100, Jürgen Purtz wrote:
> > After one week no response at all? Neither positive nor negative. It seems 
> > that
> > the community has little interest in the SVG issue. Or in my suggestion?
> 
> I have been waiting for someone to take leadership on this important
> topic, and have read your 11-page PDF with great interest.

Also, I like your idea of a default Inkscape configuration file.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+  Ancient Roman grave inscription +

Re: First SVG graphic

[Bruce Momjian]

On Wed, Nov 28, 2018 at 06:34:26PM +0100, Jürgen Purtz wrote:
> After one week no response at all? Neither positive nor negative. It seems 
> that
> the community has little interest in the SVG issue. Or in my suggestion?

I have been waiting for someone to take leadership on this important
topic, and have read your 11-page PDF with great interest.

I think your work flow on page 4 clearly illustrates that, even if we
store both native, e.g., Inkscape, and optimized SVG files, we are going
to have a problem.  If someone takes the optimized SVG file, loads it
into a tool other than the tool that created the original file, modifies
it, saves new native and optimized SVG files, and then someone goes back
to the original tool, the native file will not have the modifications
that were made by the new tool and in the new native SVG file.  This
suggests we should just use one tool to handle SVG files, probably
Inkscape.  We can consider other tools later, but let's just standardize
on one tool now and get going.  I realize you can import SVG files into
tools that did not create them, but it seems unlikely the new optimized
SVG file will appear similar to the old one.

As far as rendering in HTML, I think we have two choices:

1.  make images a link to an SVG file that can be rendered in a new
browser tab

2.  convert the SVG to PNG for HTML rendering.

I kind of prefer #2.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+  Ancient Roman grave inscription +

Re: First SVG graphic

[Jürgen Purtz]

After one week no response at all? Neither positive nor negative. It 
seems that the community has little interest in the SVG issue. Or in my 
suggestion?


Jürgen Purtz

Return codes for archive and restore commands

[PG Doc comments form]

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/11/archive-recovery-settings.html
Description:

For instance for the restore command in the documentation said: 

It is important for the command to return a zero exit status only if it
succeeds. The command will be asked for file names that are not present in
the archive; it must return nonzero when so asked. Examples:
...
An exception is that if the command was terminated by a signal (other than
SIGTERM, which is used as part of a database server shutdown) or an error by
the shell (such as command not found), then recovery will abort and the
server will not start up.
end cite

This is not correct. I think that how the behavior of PostgreSQL depends on
return codes of restore and archive commands must be more exactly explained,
this is important for those how write scripts and applications for this
commands. For instance, if the aws command line interface (awscli) used as
restore command, aws on some commands return 255 code (for instance in case
of network fault) and this leads to unexpected result with PostgreSQL.

For the archive command:
<=128 There are not errors in the PostgreSQL log (messages with severity
equal or higher than ERROR). Firstly 3 messages of type LOG about fault,
then WARNING about this and pause for 1 minute, then repeated.
>=129 FATAL error in the PostgeSQL log. The message about stoping an archive
process, but not the database. Repeated after roughly 16 seconds.

For restore command:
<=125 There are not errors in the PostgreSQL log, repeated after several
seconds. Good to return network failure or in case of absent file.
>=126 FATAL error in the PostgreSQL log, stop a startup process, shutdown
the database. Good for a fatal error, for instance misconfiguration.

In this case PostgreSQL tries confirm rules for return codes of a unix
shell. A unix shell return 126 in the case of "command not executable", 127
in the case "command not found", 128+# of signal in the case if application
interrupted by uncatched signal.