pg.ddx.io  pgsql-pkg-docker@postgresql.org mailing list archive  
help / color / mirror / Atom feed
SSH-free PostgreSQL
9+ messages / 3 participants
[nested] [flat]

* SSH-free PostgreSQL
@ 2014-07-29 18:17 Josh Berkus <josh@agliodbs.com>
  2014-07-29 19:55 ` Re: SSH-free PostgreSQL Joseph Ferguson <joe@infosiftr.com>
  0 siblings, 1 reply; 9+ messages in thread

From: Josh Berkus @ 2014-07-29 18:17 UTC (permalink / raw)
  To: pgsql-pkg-docker

All,

As I've started work on the Docker images for PostgreSQL, I've ended up
with the goal of trying to design a PostgreSQL system which doesn't
require SSHing into the server, to support the goal of one container =
one service.  I think this is also a good goal for PostgreSQL because a
server which can be run without SSHing in is more manageable at scale.

Actually, it turns out that our set of things which need shell access
for 9.4 for most users are fairly limited:

(1) start/stop/restart
(2) pg_hba.conf changes
(3) pg_basebackup
(4) replica promotion
(5) log access

For Docker at least, (1) could be handled with a container restart.
Does that make sense?

(2) I think is doable by installing the pgAdmin adminpack.  This will
also provide a postgresql.conf solution for 9.3.

(3) I'm thinking of doing this via parameterized (ENV for now) startup
of a Postgres container.  That is, you start up the container, and set
$ROLE=replica and $MASTER="connection string", and on startup it does a
basebackup and starts replication.  Make sense?

(4) I think the most sensible way to do this is via a pl/sh script, or
similar.

(5) For now we'll just make the logs output to Docker's stderr.  In
time, we'll want to figure out a way to make this work with CSVlog, but
that's a problem to solve later.

So, comments?  Ideas?

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


-- 
Sent via pgsql-pkg-docker mailing list (pgsql-pkg-docker@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-pkg-docker



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: SSH-free PostgreSQL
  2014-07-29 18:17 SSH-free PostgreSQL Josh Berkus <josh@agliodbs.com>
@ 2014-07-29 19:55 ` Joseph Ferguson <joe@infosiftr.com>
  2014-07-30 04:08   ` Re: SSH-free PostgreSQL Josh Berkus <josh@agliodbs.com>
  2014-07-30 17:55   ` Re: SSH-free PostgreSQL Josh Berkus <josh@agliodbs.com>
  0 siblings, 2 replies; 9+ messages in thread

From: Joseph Ferguson @ 2014-07-29 19:55 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-pkg-docker

On Tue, Jul 29, 2014 at 12:17 PM, Josh Berkus <josh@agliodbs.com> wrote:
> Actually, it turns out that our set of things which need shell access
> for 9.4 for most users are fairly limited:
>
> (1) start/stop/restart
> (2) pg_hba.conf changes
> (3) pg_basebackup
> (4) replica promotion
> (5) log access
>
> For Docker at least, (1) could be handled with a container restart.
> Does that make sense?

Yes, these can be handled with `docker start postgres` and `docker
stop postgres` which analogues with `service postgres start` and
`/etc/init.d/postgres stop`, and even a `docker restart postgres`
which does the relevant stop followed by start.

> (2) I think is doable by installing the pgAdmin adminpack.  This will
> also provide a postgresql.conf solution for 9.3.

This could be handled with the config file being handled through
--volumes-from or a bind mounted file/folder with -v and then doing a
docker stop and start, or `docker kill -s HUP postgres` to just send
SIGHUP for direct reload.

> (3) I'm thinking of doing this via parameterized (ENV for now) startup
> of a Postgres container.  That is, you start up the container, and set
> $ROLE=replica and $MASTER="connection string", and on startup it does a
> basebackup and starts replication.  Make sense?

Overall this is a solid idea.  While I am not terribly familiar with
postgres replication, couldn't the MASTER string be derived from
--link environment variables on the replica slave?  It would at least
have the ip address and port from the ENV.  We would also want to make
sure if they provide a ROLE for replica that they also provide the
other necessary parts (like the link if that fulfills the connection
string).  There is a similar check in the official wordpress image (to
check for the mysql --link) [1].

> (4) I think the most sensible way to do this is via a pl/sh script, or
> similar.

There are a few possibilities that I see.
 (a) startup script that forks, parent process starts postgres
normally with the passed replica slave options (for docker to track)
while child process sleeps and then runs `pg_ctl promote`
 (b) make a script for use with nsenter[2] and somehow distribute it to users
 (c) wait for `docker exec` (or similar means of officially sharing
the PID namespace of postgres, which is coming)
 (d) get pg_ctl working via "remote" socket?

I think the best would be (c) but if it is needed now, we could do (a)
or (b) as a hack until it is ready.

> (5) For now we'll just make the logs output to Docker's stderr.  In
> time, we'll want to figure out a way to make this work with CSVlog, but
> that's a problem to solve later.

Sounds good.

> So, comments?  Ideas?

Will our new branch start to fulfil the items you have here [3]?
Should we get this merged and pulling to the "official images"? Then
we can work to add the improvements you have outlined.

[1] https://github.com/docker-library/wordpress/blob/docker-3.9.1/docker-entrypoint.sh
[2] https://github.com/jpetazzo/nsenter
[3] https://github.com/infosiftr/postgres/tree/docker

- Joe Ferguson :: joe@infosiftr.com
  InfoSiftr :: Vice President of Programming


-- 
Sent via pgsql-pkg-docker mailing list (pgsql-pkg-docker@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-pkg-docker



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: SSH-free PostgreSQL
  2014-07-29 18:17 SSH-free PostgreSQL Josh Berkus <josh@agliodbs.com>
  2014-07-29 19:55 ` Re: SSH-free PostgreSQL Joseph Ferguson <joe@infosiftr.com>
@ 2014-07-30 04:08   ` Josh Berkus <josh@agliodbs.com>
  1 sibling, 0 replies; 9+ messages in thread

From: Josh Berkus @ 2014-07-30 04:08 UTC (permalink / raw)
  To: Joseph Ferguson <joe@infosiftr.com>; +Cc: pgsql-pkg-docker

On 07/29/2014 12:55 PM, Joseph Ferguson wrote:
> On Tue, Jul 29, 2014 at 12:17 PM, Josh Berkus <josh@agliodbs.com> wrote:
>> Actually, it turns out that our set of things which need shell access
>> for 9.4 for most users are fairly limited:
>>
>> (1) start/stop/restart
>> (2) pg_hba.conf changes
>> (3) pg_basebackup
>> (4) replica promotion
>> (5) log access
>>
>> For Docker at least, (1) could be handled with a container restart.
>> Does that make sense?
> 
> Yes, these can be handled with `docker start postgres` and `docker
> stop postgres` which analogues with `service postgres start` and
> `/etc/init.d/postgres stop`, and even a `docker restart postgres`
> which does the relevant stop followed by start.

Awesome!

>> (2) I think is doable by installing the pgAdmin adminpack.  This will
>> also provide a postgresql.conf solution for 9.3.
> 
> This could be handled with the config file being handled through
> --volumes-from or a bind mounted file/folder with -v and then doing a
> docker stop and start, or `docker kill -s HUP postgres` to just send
> SIGHUP for direct reload.

PostgreSQL has pg_reload_conf() already as a command, so reloading taken
care of.  And changing this stuff over port 5432 seems more elegant than
working via volumes.  Also, pgAdmin has a nice GUI for it.

> 
>> (3) I'm thinking of doing this via parameterized (ENV for now) startup
>> of a Postgres container.  That is, you start up the container, and set
>> $ROLE=replica and $MASTER="connection string", and on startup it does a
>> basebackup and starts replication.  Make sense?
> 
> Overall this is a solid idea.  While I am not terribly familiar with
> postgres replication, couldn't the MASTER string be derived from
> --link environment variables on the replica slave?  It would at least
> have the ip address and port from the ENV.  We would also want to make
> sure if they provide a ROLE for replica that they also provide the
> other necessary parts (like the link if that fulfills the connection
> string).  There is a similar check in the official wordpress image (to
> check for the mysql --link) [1].

Well, we could do that if we wanted to always assume that the --link was
called "master", which isn't a bad idea.

That does beg the question of how we could remaster (switch to a
different master), though, since --links are not changeable once the
docker has been started up.

>> (4) I think the most sensible way to do this is via a pl/sh script, or
>> similar.
> 
> There are a few possibilities that I see.
>  (a) startup script that forks, parent process starts postgres
> normally with the passed replica slave options (for docker to track)
> while child process sleeps and then runs `pg_ctl promote`
>  (b) make a script for use with nsenter[2] and somehow distribute it to users
>  (c) wait for `docker exec` (or similar means of officially sharing
> the PID namespace of postgres, which is coming)
>  (d) get pg_ctl working via "remote" socket?
> 
> I think the best would be (c) but if it is needed now, we could do (a)
> or (b) as a hack until it is ready.

That seems very complicated.  Maybe it would be easier if we used a
trigger file instead of "pg_ctl promote"?

>> (5) For now we'll just make the logs output to Docker's stderr.  In
>> time, we'll want to figure out a way to make this work with CSVlog, but
>> that's a problem to solve later.
> 
> Sounds good.
> 
>> So, comments?  Ideas?
> 
> Will our new branch start to fulfil the items you have here [3]?
> Should we get this merged and pulling to the "official images"? Then
> we can work to add the improvements you have outlined.
> 
> [1] https://github.com/docker-library/wordpress/blob/docker-3.9.1/docker-entrypoint.sh
> [2] https://github.com/jpetazzo/nsenter
> [3] https://github.com/infosiftr/postgres/tree/docker

I'll check it out.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


-- 
Sent via pgsql-pkg-docker mailing list (pgsql-pkg-docker@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-pkg-docker



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: SSH-free PostgreSQL
  2014-07-29 18:17 SSH-free PostgreSQL Josh Berkus <josh@agliodbs.com>
  2014-07-29 19:55 ` Re: SSH-free PostgreSQL Joseph Ferguson <joe@infosiftr.com>
@ 2014-07-30 17:55   ` Josh Berkus <josh@agliodbs.com>
  2014-07-30 19:34     ` Re: SSH-free PostgreSQL Joseph Ferguson <joe@infosiftr.com>
  1 sibling, 1 reply; 9+ messages in thread

From: Josh Berkus @ 2014-07-30 17:55 UTC (permalink / raw)
  To: Joseph Ferguson <joe@infosiftr.com>; +Cc: pgsql-pkg-docker

On 07/29/2014 12:55 PM, Joseph Ferguson wrote:
> Overall this is a solid idea.  While I am not terribly familiar with
> postgres replication, couldn't the MASTER string be derived from
> --link environment variables on the replica slave?  It would at least
> have the ip address and port from the ENV.  We would also want to make
> sure if they provide a ROLE for replica that they also provide the
> other necessary parts (like the link if that fulfills the connection
> string).  There is a similar check in the official wordpress image (to
> check for the mysql --link) [1].

Question: how does --link work if the two dockers are on different machines?

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


-- 
Sent via pgsql-pkg-docker mailing list (pgsql-pkg-docker@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-pkg-docker



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: SSH-free PostgreSQL
  2014-07-29 18:17 SSH-free PostgreSQL Josh Berkus <josh@agliodbs.com>
  2014-07-29 19:55 ` Re: SSH-free PostgreSQL Joseph Ferguson <joe@infosiftr.com>
  2014-07-30 17:55   ` Re: SSH-free PostgreSQL Josh Berkus <josh@agliodbs.com>
@ 2014-07-30 19:34     ` Joseph Ferguson <joe@infosiftr.com>
  2014-07-30 19:39       ` Re: SSH-free PostgreSQL Josh Berkus <josh@agliodbs.com>
  0 siblings, 1 reply; 9+ messages in thread

From: Joseph Ferguson @ 2014-07-30 19:34 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-pkg-docker

On Wed, Jul 30, 2014 at 11:55 AM, Josh Berkus <josh@agliodbs.com> wrote:
>
> Question: how does --link work if the two dockers are on different machines?

Currently it does not work at all. The local docker daemon knows
nothing of other docker daemons.  I think expanding --link or
something similar is planned for the future.

Regardless, the --link could still be emulated by setting the correct
env vars (POSTGRES_PORT_5432_TCP_ADDR POSTGRES_PORT_5432_TCP_PORT) on
the linked-from host and setting the proper routing and possible port
mapping to the container on the other machine.

- Joe Ferguson :: joe@infosiftr.com
  InfoSiftr :: Vice President of Programming


-- 
Sent via pgsql-pkg-docker mailing list (pgsql-pkg-docker@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-pkg-docker



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: SSH-free PostgreSQL
  2014-07-29 18:17 SSH-free PostgreSQL Josh Berkus <josh@agliodbs.com>
  2014-07-29 19:55 ` Re: SSH-free PostgreSQL Joseph Ferguson <joe@infosiftr.com>
  2014-07-30 17:55   ` Re: SSH-free PostgreSQL Josh Berkus <josh@agliodbs.com>
  2014-07-30 19:34     ` Re: SSH-free PostgreSQL Joseph Ferguson <joe@infosiftr.com>
@ 2014-07-30 19:39       ` Josh Berkus <josh@agliodbs.com>
  2014-07-30 19:42         ` Re: SSH-free PostgreSQL Tianon Gravi <admwiggin@gmail.com>
  0 siblings, 1 reply; 9+ messages in thread

From: Josh Berkus @ 2014-07-30 19:39 UTC (permalink / raw)
  To: Joseph Ferguson <joe@infosiftr.com>; +Cc: pgsql-pkg-docker

On 07/30/2014 12:34 PM, Joseph Ferguson wrote:
> On Wed, Jul 30, 2014 at 11:55 AM, Josh Berkus <josh@agliodbs.com> wrote:
>>
>> Question: how does --link work if the two dockers are on different machines?
> 
> Currently it does not work at all. The local docker daemon knows
> nothing of other docker daemons.  I think expanding --link or
> something similar is planned for the future.
> 
> Regardless, the --link could still be emulated by setting the correct
> env vars (POSTGRES_PORT_5432_TCP_ADDR POSTGRES_PORT_5432_TCP_PORT) on
> the linked-from host and setting the proper routing and possible port
> mapping to the container on the other machine.

Those settings aren't *enough* though.  We also need to know user and
application name.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


-- 
Sent via pgsql-pkg-docker mailing list (pgsql-pkg-docker@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-pkg-docker



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: SSH-free PostgreSQL
  2014-07-29 18:17 SSH-free PostgreSQL Josh Berkus <josh@agliodbs.com>
  2014-07-29 19:55 ` Re: SSH-free PostgreSQL Joseph Ferguson <joe@infosiftr.com>
  2014-07-30 17:55   ` Re: SSH-free PostgreSQL Josh Berkus <josh@agliodbs.com>
  2014-07-30 19:34     ` Re: SSH-free PostgreSQL Joseph Ferguson <joe@infosiftr.com>
  2014-07-30 19:39       ` Re: SSH-free PostgreSQL Josh Berkus <josh@agliodbs.com>
@ 2014-07-30 19:42         ` Tianon Gravi <admwiggin@gmail.com>
  2014-07-30 19:52           ` Re: SSH-free PostgreSQL Josh Berkus <josh@agliodbs.com>
  0 siblings, 1 reply; 9+ messages in thread

From: Tianon Gravi @ 2014-07-30 19:42 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; +Cc: Joe Ferguson <joe@infosiftr.com>; pgsql-pkg-docker

On 30 Jul 2014 13:40, "Josh Berkus" <josh@agliodbs.com> wrote:
>
> On 07/30/2014 12:34 PM, Joseph Ferguson wrote:
> > On Wed, Jul 30, 2014 at 11:55 AM, Josh Berkus <josh@agliodbs.com> wrote:
> >>
> >> Question: how does --link work if the two dockers are on different
machines?
> >
> > Currently it does not work at all. The local docker daemon knows
> > nothing of other docker daemons.  I think expanding --link or
> > something similar is planned for the future.
> >
> > Regardless, the --link could still be emulated by setting the correct
> > env vars (POSTGRES_PORT_5432_TCP_ADDR POSTGRES_PORT_5432_TCP_PORT) on
> > the linked-from host and setting the proper routing and possible port
> > mapping to the container on the other machine.
>
> Those settings aren't *enough* though.  We also need to know user and
> application name.

Well, you're not going to get that from just --link either, so I don't see
a way for those to not be specified by the user.

>
> --
> Josh Berkus
> PostgreSQL Experts Inc.
> http://pgexperts.com
>
>
> --
> Sent via pgsql-pkg-docker mailing list (pgsql-pkg-docker@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-pkg-docker

- Tianon

^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: SSH-free PostgreSQL
  2014-07-29 18:17 SSH-free PostgreSQL Josh Berkus <josh@agliodbs.com>
  2014-07-29 19:55 ` Re: SSH-free PostgreSQL Joseph Ferguson <joe@infosiftr.com>
  2014-07-30 17:55   ` Re: SSH-free PostgreSQL Josh Berkus <josh@agliodbs.com>
  2014-07-30 19:34     ` Re: SSH-free PostgreSQL Joseph Ferguson <joe@infosiftr.com>
  2014-07-30 19:39       ` Re: SSH-free PostgreSQL Josh Berkus <josh@agliodbs.com>
  2014-07-30 19:42         ` Re: SSH-free PostgreSQL Tianon Gravi <admwiggin@gmail.com>
@ 2014-07-30 19:52           ` Josh Berkus <josh@agliodbs.com>
  2014-07-30 20:09             ` Re: SSH-free PostgreSQL Joseph Ferguson <joe@infosiftr.com>
  0 siblings, 1 reply; 9+ messages in thread

From: Josh Berkus @ 2014-07-30 19:52 UTC (permalink / raw)
  To: Tianon Gravi <admwiggin@gmail.com>; +Cc: Joe Ferguson <joe@infosiftr.com>; pgsql-pkg-docker

On 07/30/2014 12:42 PM, Tianon Gravi wrote:
> On 30 Jul 2014 13:40, "Josh Berkus" <josh@agliodbs.com
> <mailto:josh@agliodbs.com>> wrote:
>>
>> On 07/30/2014 12:34 PM, Joseph Ferguson wrote:
>> > On Wed, Jul 30, 2014 at 11:55 AM, Josh Berkus <josh@agliodbs.com
> <mailto:josh@agliodbs.com>> wrote:
>> >>
>> >> Question: how does --link work if the two dockers are on different
> machines?
>> >
>> > Currently it does not work at all. The local docker daemon knows
>> > nothing of other docker daemons.  I think expanding --link or
>> > something similar is planned for the future.
>> >
>> > Regardless, the --link could still be emulated by setting the correct
>> > env vars (POSTGRES_PORT_5432_TCP_ADDR POSTGRES_PORT_5432_TCP_PORT) on
>> > the linked-from host and setting the proper routing and possible port
>> > mapping to the container on the other machine.
>>
>> Those settings aren't *enough* though.  We also need to know user and
>> application name.
> 
> Well, you're not going to get that from just --link either, so I don't
> see a way for those to not be specified by the user.

Yeah, so I think having a MASTER_CONNECT_STRING ENV specified by the
user is the way to go.  If it's populated, it's a replica.

This brings up another issue with using the Ubuntu packages.  Those
packages initdb a database directory.  Increasingly, I'm thinking that
we *don't* want the generic initdb'd directory; we want to populate it
with something more sensible (whether master or replica).  Is there a
more elegant way to do this than scripting an rm -rf?

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


-- 
Sent via pgsql-pkg-docker mailing list (pgsql-pkg-docker@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-pkg-docker



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: SSH-free PostgreSQL
  2014-07-29 18:17 SSH-free PostgreSQL Josh Berkus <josh@agliodbs.com>
  2014-07-29 19:55 ` Re: SSH-free PostgreSQL Joseph Ferguson <joe@infosiftr.com>
  2014-07-30 17:55   ` Re: SSH-free PostgreSQL Josh Berkus <josh@agliodbs.com>
  2014-07-30 19:34     ` Re: SSH-free PostgreSQL Joseph Ferguson <joe@infosiftr.com>
  2014-07-30 19:39       ` Re: SSH-free PostgreSQL Josh Berkus <josh@agliodbs.com>
  2014-07-30 19:42         ` Re: SSH-free PostgreSQL Tianon Gravi <admwiggin@gmail.com>
  2014-07-30 19:52           ` Re: SSH-free PostgreSQL Josh Berkus <josh@agliodbs.com>
@ 2014-07-30 20:09             ` Joseph Ferguson <joe@infosiftr.com>
  0 siblings, 0 replies; 9+ messages in thread

From: Joseph Ferguson @ 2014-07-30 20:09 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; +Cc: Tianon Gravi <admwiggin@gmail.com>; pgsql-pkg-docker

On Wed, Jul 30, 2014 at 1:52 PM, Josh Berkus <josh@agliodbs.com> wrote:
> This brings up another issue with using the Ubuntu packages.  Those
> packages initdb a database directory.  Increasingly, I'm thinking that
> we *don't* want the generic initdb'd directory; we want to populate it
> with something more sensible (whether master or replica).  Is there a
> more elegant way to do this than scripting an rm -rf?

The Dockerfiles from our new branch install postgresql-common first
and set the create_main_cluster to false, the initidb is then handled
in the entrypoint.  The rm -rf is no longer needed.

- Joe Ferguson :: joe@infosiftr.com
  InfoSiftr :: Vice President of Programming


-- 
Sent via pgsql-pkg-docker mailing list (pgsql-pkg-docker@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-pkg-docker



^ permalink  raw  reply  [nested|flat] 9+ messages in thread


end of thread, other threads:[~2014-07-30 20:09 UTC | newest]

Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-07-29 18:17 SSH-free PostgreSQL Josh Berkus <josh@agliodbs.com>
2014-07-29 19:55 ` Joseph Ferguson <joe@infosiftr.com>
2014-07-30 04:08   ` Josh Berkus <josh@agliodbs.com>
2014-07-30 17:55   ` Josh Berkus <josh@agliodbs.com>
2014-07-30 19:34     ` Joseph Ferguson <joe@infosiftr.com>
2014-07-30 19:39       ` Josh Berkus <josh@agliodbs.com>
2014-07-30 19:42         ` Tianon Gravi <admwiggin@gmail.com>
2014-07-30 19:52           ` Josh Berkus <josh@agliodbs.com>
2014-07-30 20:09             ` Joseph Ferguson <joe@infosiftr.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