Re: logical replication - negative bitmapset member not allowed

[Tim Clarke]

Dang. I just replicated ~380 tables. One was missing an index so I
paused replication, added a unique key on publisher and subscriber,
re-enabled replication and refreshed the subscription.

The table has only 7 columns, I added a primary key with a default value
from a new sequence.

Tim Clarke
IT Director
Direct: +44 (0)1376 504510 | Mobile: +44 (0)7887 563420

On 01/04/2019 15:02, Tom Lane wrote:
> Tim Clarke  writes:
>> I'm getting this message every 5 seconds on a single-master,
>> single-slave replication of PG10.7->PG10.7 both on Centos. Its over the
>> 'net but otherwise seems to perform excellently. Any ideas what's
>> causing it and how to fix?
> That'd certainly be a bug, but we'd need to reproduce it to fix it.
> What are you doing that's different from everybody else?  Can you
> provide any other info to narrow down the problem?
>
> regards, tom lane


Main: +44 (0)1376 503500 | Fax: +44 (0)1376 503550
Web: https://www.manifest.co.uk/



Minerva Analytics Ltd
9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | England



Copyright: This e-mail may contain confidential or legally privileged 
information. If you are not the named addressee you must not use or disclose 
such information, instead please report it to 
ad...@minerva.info
Legal:  Minerva Analytics is the trading name of: Minerva Analytics
Ltd: Registered in England Number 11260966 & The Manifest Voting Agency Ltd: 
Registered in England Number 2920820 Registered Office at above address. Please 
Click Here >> for further information.

Re: WAL Archive Cleanup?

[Foo Bar]

Hello All,

Ok, I think maybe I've been approaching this all wrong.  According to THIS
SO  post: "PostgreSQL doesn't
manage the archive directory for you, that is up to you. It doesn't even
know where (or what) the archive location is, that is why it asks you for
an archive_command, rather than just a directory."

Which I think is maybe a key component I was overlooking here.  If pgsql
doesn't know anything about the `archive/` directory, then it should be
treated and managed "out of band" from the server?

So I'm thinking something like this:

Mount NFS share to `/pgbackup/`
Master server has once-per-week `pg_basebackup` command that creates a
`/pgbkup/base_backup/` to some NFS share.
Master server has `archive_command` that creates archive in
`/pgbackup/archive`

On another server not part of the above cluster, run `pg_archivecleanup
/pgbackup/archive $(find /pgbackup/base_backup/ -name '*.backup')`
(i.e. "find the .backup file from `base_backup/` and remove any
archive files?)

Then I effectively have a place to do point-in-time restores from?

Thanks,
- QBR

On Mon, Apr 1, 2019 at 1:44 PM Foo Bar  wrote:

> Hello All,
>
> Ok, so creating the admin database has not enabled my cluster to cleanup
> the `archive/` directory...  It did eliminate the errors, though I'm
> dubious as to if that was the correct solution...
>
> Everything I'm able to google talks about setting up archive replication,
> but not how to keep that directory in check... I did fine one link
> 
> that that talks about how to cleanup obsolete archive files...  but I
> thought I didn't want to use `pg_archivecleanup`?
>
> Also, I don't really undersatnd how `pg_basebackup` is supposed to work,
> as you can only ever run it once per directory?
>
> E.g.:  if i wanted to run `pg_basebackup` weekly, I'd have to create a new
> directory every time?
>
> # pg_basebackup --verbose --host 127.0.0.1 --username replication --xlog
> --progress --pgdata /hab/svc/postgresql/data/archive/
> pg_basebackup: directory "/hab/svc/postgresql/data/archive/" exists but is
> not empty
> # pg_archivecleanup -d /hab/svc/postgresql/data/archive/
> 00010003.00D0.backup
> pg_archivecleanup: keep WAL file
> "/hab/svc/postgresql/data/archive//00010003" and later
> # ls /hab/svc/postgresql/data/archive
> 000100080081  000100080083
> 000100080085  000100080087
> 000100080089  00010008008B
> 00010008008D  00010008008F
> 000100080091  000100080093
> 000100080082  000100080084
> 000100080086  000100080088
> 00010008008A  00010008008C
> 00010008008E  000100080090  000100080092
> # rm -rf /hab/svc/postgresql/data/archive/*
> # pg_basebackup --verbose --host 127.0.0.1 --port 5432 --username
> replication --xlog --progress --pgdata /hab/svc/postgresql/data/archive/
> Password:
> pg_basebackup: initiating base backup, waiting for checkpoint to complete
> pg_basebackup: checkpoint completed
> transaction log start point: 8/9628 on timeline 1
> 45946/45946 kB (100%), 1/1 tablespace
> transaction log end point: 8/96F8
> pg_basebackup: base backup completed
> # pg_basebackup --verbose --host 127.0.0.1 --port 5432 --username
> replication --xlog --progress --pgdata /hab/svc/postgresql/data/archive/
> pg_basebackup: directory "/hab/svc/postgresql/data/archive/" exists but is
> not empty
> # pg_archivecleanup -d /hab/svc/postgresql/data/archive/
> 000100080096.0028.backup
> pg_archivecleanup: keep WAL file
> "/hab/svc/postgresql/data/archive//000100080096" and later
> pg_archivecleanup: removing file
> "/hab/svc/postgresql/data/archive//000100080095"
> # pg_basebackup --verbose --host 127.0.0.1 --port 5432 --username
> replication --xlog --progress --pgdata /hab/svc/postgresql/data/archive/
> pg_basebackup: directory "/hab/svc/postgresql/data/archive/" exists but is
> not empty
>
>
> I feel like I'm missing something really fundamental here.  Does everyone
> just have infinite storage for their `archive/` directory (that doesn't
> _seem_ plausible)?
>
> Should this maybe be stored on an off "box" location?
>
> I think I've figured out `wal_keep_segments` is for the streaming
> replication, i.e.: so I can have a hot-standby, and the `archive_command`
> is for point-in-time backups, e.g.: "oops we modified 10k rows and need to
> roll back the DB to this morning/an hour ago/20mins ago"...  (or, another
> usecase I'd really like to be able to support is to give developers a
> replica of the production database, which this seems like it might not be
> the ideal way to go for that?)
>
> Thanks!
> - QBR
>
> On Mon, Apr 1, 2019 at 10:24 AM Foo Bar 

Re: WAL Archive Cleanup?

[Foo Bar]

Hello All,

Ok, so maybe something helpful?  On my master node I am seeing a bunch of:

2019-03-28 23:54:44 GMT [2611]: [1-1]
user=admin,db=admin,client=17210.10.37 172.10.10.37(57552) (0:3D000)FATAL:
database "admin" does not exist
2019-03-28 23:54:46 GMT [2613]: [1-1]
user=admin,db=admin,client=172.10.10.18 172.10.10.18(56970)
(0:3D000)FATAL:  database "admin" does not exist

Which is accurate, as there is no admin database...  I usually connect with

psql -h localhost -U admin postgres

Should there be?  Will this fix my issue with pgsql filling up the disk?

Thanks,
- QBR

On Mon, Mar 25, 2019 at 10:21 AM Foo Bar  wrote:

> Hello All,
>
> Wow!  Lots of awesome replies, Went away for the weekend thinking my email
> had been rejected and come back to a full inbox.  Thanks for all the help!
>
> >> Postgres version?
>
> 9.6.11
>
> Doh.  Fairly important detail there.  :)
>
> >> FYI, psql is the Postgres client program, Postgres(ql) is the server.
> >> "psql" is the name of a specific command line tool used to connect to
> a PostgreSQL database server, it is not the name of the database itself.
> The database is usually abbreviated "pgsql".
>
> Duly noted, thanks for the correction.
>
> >> It's the standby that has not seen any traffic?
>
> There's really no traffic.  I built three nodes, connected them, created a
> test table and inserted some values, then left the cluster be for a couple
> weeks.
>
> >>  And "restartpoint" is usually spelled as one work in technical
> discussions of it.  Or at least, searching for it that way avoids finding
> things which mention each word separately in different senses.
>
> Ah ha.  I had seen it that way but thought it was a typo.  Thanks for the
> clarification!
>
> >> Are you sure it is the archive
> directory (/hab/svc/postgresql/data/archive) which is filling up, and not
> the live directory (pg_wal or pg_xlog)?  This is often a point of confusion.
>
> Right before I sent the mail last week I deleted everything in
> /hab/svc/postgresql/data/archive, this morning I'm seeing:
>
> # du -h --max=1 /hab/svc/postgresql/data/
> 198M /hab/svc/postgresql/data/pgdata
> 8.9G /hab/svc/postgresql/data/archive
> 9.1G /hab/svc/postgresql/data/
> # du -hs /hab/svc/postgresql/data/pgdata/pg_xlog/
> 177M /hab/svc/postgresql/data/pgdata/pg_xlog/
> # ls -lah /hab/svc/postgresql/data/archive/ | wc -l
> 571
>
> There is no pg_wal directory though (should there be?)
>
> # find /hab/svc/postgresql/ -name '*pg*wal*'
> #
>
> >>  If the only reason you want an archive is for replication, then use
> streaming replication and do away with the archive completely
>
> To be honest, I thought it was required for streaming replication based on
> the guides linked above.
>
> >> There are reasons other than replication that one might want to keep a
> WAL archive, but those reasons don't seem to apply to you
>
> Like backup maybe?  A wholly other topic, we recently had a power outage
> and I lost a pgsql node... having an archive would allow me to "replay" any
> transactions?
>
> >> What needs to be determined here is why the standby never consumed the
> WAL's from the master?
> Ok, so it the standby that's the problem.
>
> >> Do you still have the logs from the standby and do they show anything
> relevant?
>
> Sure, what am I looking for?  I see a bunch of entries like:
>
> 2019-03-08 17:06:11 GMT [1813]: [815-1] user=,db=,client=  (0:0)LOG:
> restartpoint complete: wrote 22 buffers (0.0%); 0 transaction log file(s)
> added, 0 removed, 1 recycled; write=2.211 s, sync=0.062 s, total=2.281 s;
> sync files=18, longest=0.062 s, average=0.003 s; distance=16383 kB,
> estimate=16383 kB
> 2019-03-08 17:06:11 GMT [1813]: [816-1] user=,db=,client=  (0:0)LOG:
> recovery restart point at 0/8D28
>
> On the 15th, around when I think I filled the disk, I see a bunch of:
>
> cp: cannot stat '/0002.history': No such file or directory
> cp: cannot stat '/000100040049': No such file or directory
> 2019-03-15 23:59:49 GMT [16691]: [1-1] user=,db=,client=  (0:XX000)FATAL:
> could not connect to the primary server: could not connect to server:
> Connection refused
> Is the server running on host "172.16.10.23" and accepting
> TCP/IP connections on port 5432?
>
> Which makes sense since the pgsql service was down.
>
> This appears to be when I recovered the master on Thursday:
>
> cp: cannot stat '/0002.history': No such file or directory
> cp: cannot stat '/00010004004D': No such file or directory
> 2019-03-21 17:37:31 GMT [31338]: [1-1] user=,db=,client=  (0:XX000)FATAL:
> could not connect to the primary server: could not connect to server:
> Connection refused
> Is the server running on host "172.16.10.23" and accepting
> TCP/IP connections on port 5432?
>
> cp: cannot stat '/0002.history': No such file or directory
> cp: cannot stat '/00010004004D': No such file or directory
> 2019-03-21 17:37:36 G

Re: WAL Archive Cleanup?

[Foo Bar]

Hello All,

Ok, so creating the admin database has not enabled my cluster to cleanup
the `archive/` directory...  It did eliminate the errors, though I'm
dubious as to if that was the correct solution...

Everything I'm able to google talks about setting up archive replication,
but not how to keep that directory in check... I did fine one link

that that talks about how to cleanup obsolete archive files...  but I
thought I didn't want to use `pg_archivecleanup`?

Also, I don't really undersatnd how `pg_basebackup` is supposed to work, as
you can only ever run it once per directory?

E.g.:  if i wanted to run `pg_basebackup` weekly, I'd have to create a new
directory every time?

# pg_basebackup --verbose --host 127.0.0.1 --username replication --xlog
--progress --pgdata /hab/svc/postgresql/data/archive/
pg_basebackup: directory "/hab/svc/postgresql/data/archive/" exists but is
not empty
# pg_archivecleanup -d /hab/svc/postgresql/data/archive/
00010003.00D0.backup
pg_archivecleanup: keep WAL file
"/hab/svc/postgresql/data/archive//00010003" and later
# ls /hab/svc/postgresql/data/archive
000100080081  000100080083
000100080085  000100080087
000100080089  00010008008B
00010008008D  00010008008F
000100080091  000100080093
000100080082  000100080084
000100080086  000100080088
00010008008A  00010008008C
00010008008E  000100080090  000100080092
# rm -rf /hab/svc/postgresql/data/archive/*
# pg_basebackup --verbose --host 127.0.0.1 --port 5432 --username
replication --xlog --progress --pgdata /hab/svc/postgresql/data/archive/
Password:
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
transaction log start point: 8/9628 on timeline 1
45946/45946 kB (100%), 1/1 tablespace
transaction log end point: 8/96F8
pg_basebackup: base backup completed
# pg_basebackup --verbose --host 127.0.0.1 --port 5432 --username
replication --xlog --progress --pgdata /hab/svc/postgresql/data/archive/
pg_basebackup: directory "/hab/svc/postgresql/data/archive/" exists but is
not empty
# pg_archivecleanup -d /hab/svc/postgresql/data/archive/
000100080096.0028.backup
pg_archivecleanup: keep WAL file
"/hab/svc/postgresql/data/archive//000100080096" and later
pg_archivecleanup: removing file
"/hab/svc/postgresql/data/archive//000100080095"
# pg_basebackup --verbose --host 127.0.0.1 --port 5432 --username
replication --xlog --progress --pgdata /hab/svc/postgresql/data/archive/
pg_basebackup: directory "/hab/svc/postgresql/data/archive/" exists but is
not empty


I feel like I'm missing something really fundamental here.  Does everyone
just have infinite storage for their `archive/` directory (that doesn't
_seem_ plausible)?

Should this maybe be stored on an off "box" location?

I think I've figured out `wal_keep_segments` is for the streaming
replication, i.e.: so I can have a hot-standby, and the `archive_command`
is for point-in-time backups, e.g.: "oops we modified 10k rows and need to
roll back the DB to this morning/an hour ago/20mins ago"...  (or, another
usecase I'd really like to be able to support is to give developers a
replica of the production database, which this seems like it might not be
the ideal way to go for that?)

Thanks!
- QBR

On Mon, Apr 1, 2019 at 10:24 AM Foo Bar  wrote:

> Hello All,
>
> Ok, so maybe something helpful?  On my master node I am seeing a bunch of:
>
> 2019-03-28 23:54:44 GMT [2611]: [1-1]
> user=admin,db=admin,client=17210.10.37 172.10.10.37(57552) (0:3D000)FATAL:
> database "admin" does not exist
> 2019-03-28 23:54:46 GMT [2613]: [1-1]
> user=admin,db=admin,client=172.10.10.18 172.10.10.18(56970)
> (0:3D000)FATAL:  database "admin" does not exist
>
> Which is accurate, as there is no admin database...  I usually connect
> with
>
> psql -h localhost -U admin postgres
>
> Should there be?  Will this fix my issue with pgsql filling up the disk?
>
> Thanks,
> - QBR
>
> On Mon, Mar 25, 2019 at 10:21 AM Foo Bar  wrote:
>
>> Hello All,
>>
>> Wow!  Lots of awesome replies, Went away for the weekend thinking my
>> email had been rejected and come back to a full inbox.  Thanks for all the
>> help!
>>
>> >> Postgres version?
>>
>> 9.6.11
>>
>> Doh.  Fairly important detail there.  :)
>>
>> >> FYI, psql is the Postgres client program, Postgres(ql) is the server.
>> >> "psql" is the name of a specific command line tool used to connect to
>> a PostgreSQL database server, it is not the name of the database itself.
>> The database is usually abbreviated "pgsql".
>>
>> Duly noted, thanks for the correction.
>>
>> >> It's the standby that has not seen any t

Re: WAL Archive Cleanup?

[Alban Hertroys]

That seems to be a misconfigured client application that explicitly tries
to connect to a non-existent database 'admin' (via db=admin).
Instead of adding that database, it seems more logical to fix the client
configuration.

On Tue, 2 Apr 2019 at 09:53, Foo Bar  wrote:

> Hello All,
>
> Ok, so maybe something helpful?  On my master node I am seeing a bunch of:
>
> 2019-03-28 23:54:44 GMT [2611]: [1-1]
> user=admin,db=admin,client=17210.10.37 172.10.10.37(57552) (0:3D000)FATAL:
> database "admin" does not exist
> 2019-03-28 23:54:46 GMT [2613]: [1-1]
> user=admin,db=admin,client=172.10.10.18 172.10.10.18(56970)
> (0:3D000)FATAL:  database "admin" does not exist
>
> Which is accurate, as there is no admin database...  I usually connect
> with
>
> psql -h localhost -U admin postgres
>
> Should there be?  Will this fix my issue with pgsql filling up the disk?
>
> Thanks,
> - QBR
>
> On Mon, Mar 25, 2019 at 10:21 AM Foo Bar  wrote:
>
>> Hello All,
>>
>> Wow!  Lots of awesome replies, Went away for the weekend thinking my
>> email had been rejected and come back to a full inbox.  Thanks for all the
>> help!
>>
>> >> Postgres version?
>>
>> 9.6.11
>>
>> Doh.  Fairly important detail there.  :)
>>
>> >> FYI, psql is the Postgres client program, Postgres(ql) is the server.
>> >> "psql" is the name of a specific command line tool used to connect to
>> a PostgreSQL database server, it is not the name of the database itself.
>> The database is usually abbreviated "pgsql".
>>
>> Duly noted, thanks for the correction.
>>
>> >> It's the standby that has not seen any traffic?
>>
>> There's really no traffic.  I built three nodes, connected them, created
>> a test table and inserted some values, then left the cluster be for a
>> couple weeks.
>>
>> >>  And "restartpoint" is usually spelled as one work in technical
>> discussions of it.  Or at least, searching for it that way avoids finding
>> things which mention each word separately in different senses.
>>
>> Ah ha.  I had seen it that way but thought it was a typo.  Thanks for the
>> clarification!
>>
>> >> Are you sure it is the archive
>> directory (/hab/svc/postgresql/data/archive) which is filling up, and not
>> the live directory (pg_wal or pg_xlog)?  This is often a point of confusion.
>>
>> Right before I sent the mail last week I deleted everything in
>> /hab/svc/postgresql/data/archive, this morning I'm seeing:
>>
>> # du -h --max=1 /hab/svc/postgresql/data/
>> 198M /hab/svc/postgresql/data/pgdata
>> 8.9G /hab/svc/postgresql/data/archive
>> 9.1G /hab/svc/postgresql/data/
>> # du -hs /hab/svc/postgresql/data/pgdata/pg_xlog/
>> 177M /hab/svc/postgresql/data/pgdata/pg_xlog/
>> # ls -lah /hab/svc/postgresql/data/archive/ | wc -l
>> 571
>>
>> There is no pg_wal directory though (should there be?)
>>
>> # find /hab/svc/postgresql/ -name '*pg*wal*'
>> #
>>
>> >>  If the only reason you want an archive is for replication, then use
>> streaming replication and do away with the archive completely
>>
>> To be honest, I thought it was required for streaming replication based
>> on the guides linked above.
>>
>> >> There are reasons other than replication that one might want to keep a
>> WAL archive, but those reasons don't seem to apply to you
>>
>> Like backup maybe?  A wholly other topic, we recently had a power outage
>> and I lost a pgsql node... having an archive would allow me to "replay" any
>> transactions?
>>
>> >> What needs to be determined here is why the standby never consumed
>> the WAL's from the master?
>> Ok, so it the standby that's the problem.
>>
>> >> Do you still have the logs from the standby and do they show anything
>> relevant?
>>
>> Sure, what am I looking for?  I see a bunch of entries like:
>>
>> 2019-03-08 17:06:11 GMT [1813]: [815-1] user=,db=,client=  (0:0)LOG:
>> restartpoint complete: wrote 22 buffers (0.0%); 0 transaction log file(s)
>> added, 0 removed, 1 recycled; write=2.211 s, sync=0.062 s, total=2.281 s;
>> sync files=18, longest=0.062 s, average=0.003 s; distance=16383 kB,
>> estimate=16383 kB
>> 2019-03-08 17:06:11 GMT [1813]: [816-1] user=,db=,client=  (0:0)LOG:
>> recovery restart point at 0/8D28
>>
>> On the 15th, around when I think I filled the disk, I see a bunch of:
>>
>> cp: cannot stat '/0002.history': No such file or directory
>> cp: cannot stat '/000100040049': No such file or directory
>> 2019-03-15 23:59:49 GMT [16691]: [1-1] user=,db=,client=
>> (0:XX000)FATAL:  could not connect to the primary server: could not connect
>> to server: Connection refused
>> Is the server running on host "172.16.10.23" and accepting
>> TCP/IP connections on port 5432?
>>
>> Which makes sense since the pgsql service was down.
>>
>> This appears to be when I recovered the master on Thursday:
>>
>> cp: cannot stat '/0002.history': No such file or directory
>> cp: cannot stat '/00010004004D': No such file or directory
>> 2019-03-21 17:37:31 GMT [31338]: [1-1] user=,db

Re: CVE-2019-9193 about COPY FROM/TO PROGRAM

[Brad Nicholson]

Michael Paquier  wrote on 04/02/2019 01:05:01 AM:

> From: Michael Paquier 
> To: "Jonathan S. Katz" 
> Cc: Tom Lane , Magnus Hagander
> , Daniel Verite ,
> pgsql-general 
> Date: 04/02/2019 01:05 AM
> Subject: Re: CVE-2019-9193 about COPY FROM/TO PROGRAM
>
> On Mon, Apr 01, 2019 at 10:04:32AM -0400, Jonathan S. Katz wrote:
> > +1, though I’d want to see if people get noisier about it before we
rule
> > out an official response.
> >
> > A blog post from a reputable author who can speak to security should
> > be good enough and we can make noise through our various channels.
>
> Need a hand?  Not sure if I am reputable enough though :)
>
> By the way, it could be the occasion to consider an official
> PostgreSQL blog on the main website.  News are not really a model
> adapted for problem analysis and for entering into technical details.

A blog post would be nice, but it seems to me have something about this
clearly in the manual would be best, assuming it's not there already.  I
took a quick look, and couldn't find anything.

Brad

Re: CVE-2019-9193 about COPY FROM/TO PROGRAM

[Jonathan S. Katz]

On 4/2/19 1:05 AM, Michael Paquier wrote:
> On Mon, Apr 01, 2019 at 10:04:32AM -0400, Jonathan S. Katz wrote:
>> +1, though I’d want to see if people get noisier about it before we rule
>> out an official response.
>>
>> A blog post from a reputable author who can speak to security should
>> be good enough and we can make noise through our various channels.
> 
> Need a hand?  Not sure if I am reputable enough though :)

I believe you are, and any blog entries helping the matter are welcome :)

> By the way, it could be the occasion to consider an official
> PostgreSQL blog on the main website.  News are not really a model
> adapted for problem analysis and for entering into technical details.

I think this is warrants a longer discussion, albeit for a different day.

Jonathan



signature.asc
Description: OpenPGP digital signature

Re: logical replication - negative bitmapset member not allowed

[Tom Lane]

Tim Clarke  writes:
> Dang. I just replicated ~380 tables. One was missing an index so I
> paused replication, added a unique key on publisher and subscriber,
> re-enabled replication and refreshed the subscription.

Well, that's not much help :-(.  Can you provide any info to narrow
down where this is happening?  I mean, you haven't even told us whether
it's the primary or the slave that is complaining.  Does it seem to
be associated with any particular command?  (Turning on log_statement
and/or log_replication_commands would likely help with that.)  Does
data seem to be getting transferred despite the complaint?  If not,
what's missing on the slave?

regards, tom lane

Re: logical replication - negative bitmapset member not allowed

[Tim Clarke]

On 02/04/2019 14:59, Tom Lane wrote:
> Well, that's not much help :-(.  Can you provide any info to narrow
> down where this is happening?  I mean, you haven't even told us whether
> it's the primary or the slave that is complaining.  Does it seem to
> be associated with any particular command?  (Turning on log_statement
> and/or log_replication_commands would likely help with that.)  Does
> data seem to be getting transferred despite the complaint?  If not,
> what's missing on the slave?
>
> regards, tom lane


I've been working to narrow it, the error is being reported on the slave.

The only schema changes have been the two primary keys added to two
tables. The problem occurred during this cycle:

1) Replication proceeding fine for ~380 tables, all added individually
not "all tables".

2) Add primary key on master.

3) Add primary key on slave.

4) Refresh subscription on slave; error starts being reported.

I've cleared it by dropping the slave database, re-creating from the
live schema then fully replicating. Its all running happily now.


Tim Clarke



Main: +44 (0)1376 503500 | Fax: +44 (0)1376 503550
Web: https://www.manifest.co.uk/



Minerva Analytics Ltd
9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | England



Copyright: This e-mail may contain confidential or legally privileged 
information. If you are not the named addressee you must not use or disclose 
such information, instead please report it to 
ad...@minerva.info
Legal:  Minerva Analytics is the trading name of: Minerva Analytics
Ltd: Registered in England Number 11260966 & The Manifest Voting Agency Ltd: 
Registered in England Number 2920820 Registered Office at above address. Please 
Click Here >> for further information.

Re: Fwd: Postgresql with nextcloud in Windows Server

[Adrian Klaver]

On 4/1/19 10:03 PM, 김준형 wrote:


보낸사람: *김준형* mailto:wnsuddl...@gmail.com>>
Date: 2019년 4월 2일 (화) 오후 2:02
Subject: Re: Postgresql with nextcloud in Windows Server
To: Adrian Klaver >



Thanks for your reply and I hope this answers can help your questions

1) The nextcloud is running on PostgreSQL server. Cloud system needs 
PostgreSQL server.


Well I got that backwards. Forget that Nextcloud is an ownCloud fork.



2) Nextcloud system try to connect PostgreSQL server all time.
2019-03-27 20:46:59.396 LOG:  connection received: host=xxx.xxx.xxx.xxx 
port=
2019-03-27 20:46:59.403 LOG:  connection authorized: user=user_name 
database=db_name
2019-03-27 20:46:59.463 LOG:  disconnection: session time: 0:00:00.067 
user=user_name database=db_name host=xxx.xxx.xxx.xxx port=

this connections repeat almost per 10sec.
Other clients well... use this PostgreSQL but not so much.(almost 30 
people use this PostgreSQL include nextcloud system users)


There is nothing unusual about the above, just shows a client 
successfully connecting and then disconnecting. I set up an ownCloud 
server years ago as a test and as I remember it was constantly talking 
to the Postgres server. In postgresql.conf you could set log_statement = 
'all' to see what is actually being done by the client(s). Just be aware 
this will generate a lot of logs so you probably want to do this for 
short period only.





3) Yes. log files doesn't shows problems clearly. I just checked log 
files and saw difference when server couldn't connected.
2019-03-27 20:46:59.396 LOG:  connection received: host=xxx.xxx.xxx.xxx 
port=
2019-03-27 20:46:59.403 LOG:  connection authorized: user=user_name 
database=db_name

this log repeated and no disconnection log.


This shows a successful connection. The disconnection maybe much further 
in the future or has not happened at all. To see current connections 
select from pg_stat_activity:


www.postgresql.org/docs/11/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW



4) After problem occur, if try to connect to windows server, windows 
remote access shows time-out error. PostgreSQL server also too.


What problem occurs?
Where is the Windows server?



5) Before the server doesn't work normally, there is no problem to use 
PostgreSQL (even nextcloud system, too.)


6) No, It doesn't work. PostgreSQL service status doesn't changed.


Not sure you know that if you cannot connect to the Windows server?



7) When I restart server, I check PostgreSQL data and I see some schema 
data disappeared(only data). log files says 'db system was not properly 
shut down' so 'automatic recover in progress' when PostgreSQL server 
started after Windows Server get restarted.
I think this 'not properly shut down' causes windows server cold 
booting.(I need to turn on the server quickly for some reason and my 
server spends a lot of time to restart.)


To me it looks like the OS is crashing and bringing the Postgres server 
down with it. There is a chance it is the other way around. To figure 
this out I would suggest looking at what is the below just before/at 
time of crash/after restart:


1) The Windows system logs

2) The Postgres log



2019년 4월 2일 (화) 오후 1:21, Adrian Klaver >님이 작성:


On 4/1/19 6:21 PM, 김준형 wrote:
 > Hi Community, I have problem so I wanna help from PostgreSQL
community.
 > My problem is using PostgreSQL with nextcloud(cloud system) and
It can
 > make no more connection to server. I checked log files and knew
reason.
 > PostgreSQL prevented to disconnecting connection but still tried to
 > connect PostgreSQL server. This problem blocks to connect server and
 > can't stop PostgreSQL service. What I can do is just restart the
server
 > and recover PostgreSQL data(I don't know why some data disappear.).
 > Can I get some help about this problem?

Not sure I entirely understand the above, so to help can you answer the
following:

1) The Postgres server is running on nextcloud, correct?

2) The client you are using to connect to the server is on nextcloud
also or somewhere else?

3) The log files do not show a problem, is this correct?

4) Do you see an error message on the client end when you try to
connect?

5) Have you looked at your pg_hba.conf to see if allows connections
from
your client?

6) Not sure what you mean when you say you cannot stop the service, but
that you can restart it?

7) How do you know that data has disappeared?

 >
 > My OS and PostgreSQL version is
 > PostgreSQL 10.5
 > Windows Server 2012 R2
 >


-- 
Adrian Klaver

adrian.kla...@aklaver.com 




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: logical replication - negative bitmapset member not allowed

[Tom Lane]

Tim Clarke  writes:
> I've cleared it by dropping the slave database, re-creating from the
> live schema then fully replicating. Its all running happily now.

I'm glad you're out of the woods, but we still have a bug there
waiting to bite the next person.  I wonder if you'd be willing to
spend some time trying to develop a reproduction sequence for this
(obviously, working on a test setup not your live servers).
Presumably there's something in the subscription-alteration logic
that needs work, but I don't think we have enough detail here for
somebody else to reproduce the error without a lot of guesswork.

regards, tom lane

Running psql in emacs shell generates key conflict error: why?

[Rich Shepard]

An unexpected result this morning that I'd like to understand. In emacs I
loaded the file organizations.sql in a buffer to insert new rows in that
table. Rather than running psql from a virtual console, I opened an emacs
shell and entered the command,
  psql -f organizations.sql -d bustrac
and saw an error that the last line in the .sql file was in conflict with an
existing primary key. (I let postgres assign the keys when new rows are
inserted.)

Checking the table for that primary key confirmed that it was a different
organization so there should not have been a conflict.

Using a virtual console, and in the directory with the scripts, I ran the
same psql command and it completed as expected.

I don't think this is a emacs error because the shell opens in the same
directory as the script I'm running.

Why might postgres fail to run the script within an emacs shell while having
no issues running the same script from a virtual console?

A curious mind wants to learn.

TIA,

Rich

Re: logical replication - negative bitmapset member not allowed

[Tim Clarke]

On 02/04/2019 15:46, Tom Lane wrote:
> I'm glad you're out of the woods, but we still have a bug there
> waiting to bite the next person.  I wonder if you'd be willing to
> spend some time trying to develop a reproduction sequence for this
> (obviously, working on a test setup not your live servers).
> Presumably there's something in the subscription-alteration logic
> that needs work, but I don't think we have enough detail here for
> somebody else to reproduce the error without a lot of guesswork.
>
> regards, tom lane


I'll do what I can :)


Tim Clarke



Main: +44 (0)1376 503500 | Fax: +44 (0)1376 503550
Web: https://www.manifest.co.uk/



Minerva Analytics Ltd
9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | England



Copyright: This e-mail may contain confidential or legally privileged 
information. If you are not the named addressee you must not use or disclose 
such information, instead please report it to 
ad...@minerva.info
Legal:  Minerva Analytics is the trading name of: Minerva Analytics
Ltd: Registered in England Number 11260966 & The Manifest Voting Agency Ltd: 
Registered in England Number 2920820 Registered Office at above address. Please 
Click Here >> for further information.

Re: CVE-2019-9193 about COPY FROM/TO PROGRAM

[Andres Freund]

Hi,

On 2019-04-02 07:35:02 -0500, Brad Nicholson wrote:
> Michael Paquier  wrote on 04/02/2019 01:05:01 AM:
> 
> > From: Michael Paquier 
> > To: "Jonathan S. Katz" 
> > Cc: Tom Lane , Magnus Hagander
> > , Daniel Verite ,
> > pgsql-general 
> > Date: 04/02/2019 01:05 AM
> > Subject: Re: CVE-2019-9193 about COPY FROM/TO PROGRAM
> >
> > On Mon, Apr 01, 2019 at 10:04:32AM -0400, Jonathan S. Katz wrote:
> > > +1, though I’d want to see if people get noisier about it before we
> rule
> > > out an official response.
> > >
> > > A blog post from a reputable author who can speak to security should
> > > be good enough and we can make noise through our various channels.
> >
> > Need a hand?  Not sure if I am reputable enough though :)
> >
> > By the way, it could be the occasion to consider an official
> > PostgreSQL blog on the main website.  News are not really a model
> > adapted for problem analysis and for entering into technical details.
> 
> A blog post would be nice, but it seems to me have something about this
> clearly in the manual would be best, assuming it's not there already.  I
> took a quick look, and couldn't find anything.

https://www.postgresql.org/docs/devel/sql-copy.html

"Note that the command is invoked by the shell, so if you need to pass
any arguments to shell command that come from an untrusted source, you
must be careful to strip or escape any special characters that might
have a special meaning for the shell. For security reasons, it is best
to use a fixed command string, or at least avoid passing any user input
in it."

"Similarly, the command specified with PROGRAM is executed directly by
the server, not by the client application, must be executable by the
PostgreSQL user. COPY naming a file or command is only allowed to
database superusers or users who are granted one of the default roles
pg_read_server_files, pg_write_server_files, or
pg_execute_server_program, since it allows reading or writing any file
or running a program that the server has privileges to access."

Those seem reasonable to me?

Greetings,

Andres Freund

Re: [SPAM] Re: Key encryption and relational integrity

[Moreno Andreo]

Il 01/04/2019 20:48, Rory Campbell-Lange ha scritto:

On 01/04/19, Moreno Andreo (moreno.and...@evolu-s.it) wrote:
...

I'm not forced to use pseudonimysation if there's the risk to get
things worse in a system. I've got to speak about these"two opposing
forces at work" to a privacy expert (maybe choosing another one, as
Peter suggested :-) ) and ask him if it could be used as a matter of
declining pseudonymisation because of "pseudonimysation puts at risk
overall performance or database integrity"

How to interpret the pseudonymisation conditions is ... complicated.

Yes, it is indeed... :-)

  The
UK's Information Commissioner's Office (ICO) writes that
pseudoanonymisation relates to:

 “…the processing of personal data in such a manner that the personal
 data can no longer be attributed to a specific data subject without
 the use of additional information, provided that such additional
 information is kept separately and is subject to technical and
 organisational measures to ensure that the personal data are not
 attributed to an identified or identifiable natural person.”

and that this "...can reduce the risks to the data subjects".

The concept of application realms may be relevant to consider here. An
application may be considered GDPR compliant without pseudonymisation if
other measures are taken and the use case is appropriate.
That could be my case, so I'll have to discuss the strategy and measures 
to be adopted with a privacy consultant.


On the other hand, a copy of a production database in testing which has
been pseudonymised may, if compromised, still leak personal data. As the
ICO states:

 “…Personal data which have undergone pseudonymisation, which could
 be attributed to a natural person by the use of additional
 information should be considered to be information on an
 identifiable natural person…”

https://ico.org.uk/for-organisations/guide-to-data-protection/guide-to-the-general-data-protection-regulation-gdpr/what-is-personal-data/what-is-personal-data/

If leakage occurs pseudonymisation has achieved nothing.


That's another aspect of the question.

Thanks for the clarification,

Moreno.-

Re: CVE-2019-9193 about COPY FROM/TO PROGRAM

[Magnus Hagander]

On Tue, Apr 2, 2019 at 5:31 PM Andres Freund  wrote:

> Hi,
>
> On 2019-04-02 07:35:02 -0500, Brad Nicholson wrote:
> > Michael Paquier  wrote on 04/02/2019 01:05:01 AM:
> >
> > > From: Michael Paquier 
> > > To: "Jonathan S. Katz" 
> > > Cc: Tom Lane , Magnus Hagander
> > > , Daniel Verite ,
> > > pgsql-general 
> > > Date: 04/02/2019 01:05 AM
> > > Subject: Re: CVE-2019-9193 about COPY FROM/TO PROGRAM
> > >
> > > On Mon, Apr 01, 2019 at 10:04:32AM -0400, Jonathan S. Katz wrote:
> > > > +1, though I’d want to see if people get noisier about it before we
> > rule
> > > > out an official response.
> > > >
> > > > A blog post from a reputable author who can speak to security should
> > > > be good enough and we can make noise through our various channels.
> > >
> > > Need a hand?  Not sure if I am reputable enough though :)
> > >
> > > By the way, it could be the occasion to consider an official
> > > PostgreSQL blog on the main website.  News are not really a model
> > > adapted for problem analysis and for entering into technical details.
> >
> > A blog post would be nice, but it seems to me have something about this
> > clearly in the manual would be best, assuming it's not there already.  I
> > took a quick look, and couldn't find anything.
>
> https://www.postgresql.org/docs/devel/sql-copy.html
>
> "Note that the command is invoked by the shell, so if you need to pass
> any arguments to shell command that come from an untrusted source, you
> must be careful to strip or escape any special characters that might
> have a special meaning for the shell. For security reasons, it is best
> to use a fixed command string, or at least avoid passing any user input
> in it."
>
> "Similarly, the command specified with PROGRAM is executed directly by
> the server, not by the client application, must be executable by the
> PostgreSQL user. COPY naming a file or command is only allowed to
> database superusers or users who are granted one of the default roles
> pg_read_server_files, pg_write_server_files, or
> pg_execute_server_program, since it allows reading or writing any file
> or running a program that the server has privileges to access."
>
> Those seem reasonable to me?
>

Agreed, that part can't really be much clearer.

But perhaps we should add a warning box to
https://www.postgresql.org/docs/11/sql-createrole.html that basically says
"creating a superuser means they can x, y and z"?

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

Re: CVE-2019-9193 about COPY FROM/TO PROGRAM

[Jonathan S. Katz]

On 4/2/19 2:08 PM, Magnus Hagander wrote:
> On Tue, Apr 2, 2019 at 5:31 PM Andres Freund  > wrote:
> 
> Hi,
> 
> On 2019-04-02 07:35:02 -0500, Brad Nicholson wrote:
> > Michael Paquier mailto:mich...@paquier.xyz>>
> wrote on 04/02/2019 01:05:01 AM:
> >
> > > From: Michael Paquier  >
> > > To: "Jonathan S. Katz"  >
> > > Cc: Tom Lane mailto:t...@sss.pgh.pa.us>>,
> Magnus Hagander
> > > mailto:mag...@hagander.net>>, Daniel
> Verite mailto:dan...@manitou-mail.org>>,
> > > pgsql-general  >
> > > Date: 04/02/2019 01:05 AM
> > > Subject: Re: CVE-2019-9193 about COPY FROM/TO PROGRAM
> > >
> > > On Mon, Apr 01, 2019 at 10:04:32AM -0400, Jonathan S. Katz wrote:
> > > > +1, though I’d want to see if people get noisier about it
> before we
> > rule
> > > > out an official response.
> > > >
> > > > A blog post from a reputable author who can speak to security
> should
> > > > be good enough and we can make noise through our various channels.
> > >
> > > Need a hand?  Not sure if I am reputable enough though :)
> > >
> > > By the way, it could be the occasion to consider an official
> > > PostgreSQL blog on the main website.  News are not really a model
> > > adapted for problem analysis and for entering into technical
> details.
> >
> > A blog post would be nice, but it seems to me have something about
> this
> > clearly in the manual would be best, assuming it's not there
> already.  I
> > took a quick look, and couldn't find anything.
> 
> https://www.postgresql.org/docs/devel/sql-copy.html
> 
> "Note that the command is invoked by the shell, so if you need to pass
> any arguments to shell command that come from an untrusted source, you
> must be careful to strip or escape any special characters that might
> have a special meaning for the shell. For security reasons, it is best
> to use a fixed command string, or at least avoid passing any user input
> in it."
> 
> "Similarly, the command specified with PROGRAM is executed directly by
> the server, not by the client application, must be executable by the
> PostgreSQL user. COPY naming a file or command is only allowed to
> database superusers or users who are granted one of the default roles
> pg_read_server_files, pg_write_server_files, or
> pg_execute_server_program, since it allows reading or writing any file
> or running a program that the server has privileges to access."
> 
> Those seem reasonable to me?
> 
> 
> Agreed, that part can't really be much clearer.
> 
> But perhaps we should add a warning box
> to https://www.postgresql.org/docs/11/sql-createrole.html that basically
> says "creating a superuser means they can x, y and z"?

Yeah, I think that's the path forward -- make it much clearer by putting
it in the warning box and just re-stating that this is what it means.

Jonathan



signature.asc
Description: OpenPGP digital signature

max_prepared_foreign_transactions is unrecognized

[Eric J. Van der Velden]

On https://wiki.postgresql.org/wiki/2PC_on_FDW it says I have to set the
configuration parameter "max_prepared_foreign_transactions"  in
postgresql.conf. When I restart postgres, I see:

LOG:  unrecognized configuration parameter
"max_prepared_foreign_transactions" in file
"/var/lib/postgresql/data/postgresql.conf" line 689

Thanks,
Eric.

Re: New LLVM JIT Features

[Praveen Velliengiri]

hi
I'm following up on this request, please feel free to reply

On Apr 2, 2019 12:51 AM, "preejackie"  wrote:

> Hi
>
> I'm Praveen Velliengiri, student from India. I'm working on developing a
> Speculative compilation support in LLVM ORC JIT Infrastructure.
>
> As LLVM ORC supports compiling in multiple backend threads, it would be
> effective if we compile the functions speculatively before they are called
> by the executing function. So when we request JIT to compile a function,
> JIT will immediately returns the function address for raw executable bits.
> This will greatly reduce the JIT latencies in modern multi-core machines.
> And also I'm working on designing a ORC in-place dynamic profiling support,
> by this JIT will automatically able to identify the hot functions, and
> compile it in higher optimization level to achieve good performance.
>
> I'm proposing this project for GSoC 2019. It would be helpful to know how
> this new features are effective to pgsql engine, so that I include your
> comments in "View from Clients" proposal section.
>
> Please reply :)
> --
>
> Have a great day!
> PreeJackie
>
>

Re: CVE-2019-9193 about COPY FROM/TO PROGRAM

[Magnus Hagander]

On Mon, Apr 1, 2019 at 4:04 PM Jonathan S. Katz 
wrote:

>
> > On Apr 1, 2019, at 9:55 AM, Tom Lane  wrote:
> >
> > Magnus Hagander  writes:
> >>> On Sat, Mar 30, 2019 at 10:16 PM Tom Lane  wrote:
> >>> Yeah; this is supposing that there is a security boundary between
> >>> Postgres superusers and the OS account running the server, which
> >>> there is not.  We could hardly have features like untrusted PLs
> >>> if we were trying to maintain such a boundary.
> >
> >> I wonder if we need to prepare some sort of official response to that.
> >> I was considering writing up a blog post about it, but maybe we need
> >> something more official?
> >
> > Blog post seems like a good idea.  As for an "official" response,
> > it strikes me that maybe we need better documentation.
>
> +1, though I’d want to see if people get noisier about it before we rule
> out an official response.
>
> A blog post from a reputable author who can speak to security should
> be good enough and we can make noise through our various channels.
>

I have now made such a post at
https://blog.hagander.net/when-a-vulnerability-is-not-a-vulnerability-244/

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

Re: max_prepared_foreign_transactions is unrecognized

[Adrian Klaver]

On 4/2/19 12:36 PM, Eric J. Van der Velden wrote:
On https://wiki.postgresql.org/wiki/2PC_on_FDW it says I have to set the 
configuration parameter "max_prepared_foreign_transactions"  in 
postgresql.conf. When I restart postgres, I see:


From what I see the above was a proposal that did not actually get 
implemented, at least in the form above.




LOG:  unrecognized configuration parameter 
"max_prepared_foreign_transactions" in file 


Which is true as I see no such GUC here:

https://www.postgresql.org/docs/11/runtime-config.html


"/var/lib/postgresql/data/postgresql.conf" line 689

Thanks,
Eric.



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Running psql in emacs shell generates key conflict error: why?

[Adrian Klaver]

On 4/2/19 8:00 AM, Rich Shepard wrote:

An unexpected result this morning that I'd like to understand. In emacs I
loaded the file organizations.sql in a buffer to insert new rows in that
table. Rather than running psql from a virtual console, I opened an emacs
shell and entered the command,
   psql -f organizations.sql -d bustrac
and saw an error that the last line in the .sql file was in conflict 
with an

existing primary key. (I let postgres assign the keys when new rows are
inserted.)


What was the exact message?

What was in the last line?



Checking the table for that primary key confirmed that it was a different
organization so there should not have been a conflict.

Using a virtual console, and in the directory with the scripts, I ran the
same psql command and it completed as expected.

I don't think this is a emacs error because the shell opens in the same
directory as the script I'm running.

Why might postgres fail to run the script within an emacs shell while 
having

no issues running the same script from a virtual console?


Difference in shell escaping?




A curious mind wants to learn.

TIA,

Rich






--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Running psql in emacs shell generates key conflict error: why? [FIXED]

[Rich Shepard]

On Tue, 2 Apr 2019, Adrian Klaver wrote:


What was the exact message?
What was in the last line?
Difference in shell escaping?


Adrian,

Cannot answer the first two because that work is long gone. So, I just tried
it again with only one row to insert and psql inserted that row without
complaint.

Let's consider it a fluke unless it happens again.

Regards,

Rich

Re: max_prepared_foreign_transactions is unrecognized

[Adrian Klaver]

On 4/2/19 1:02 PM, Eric J. Van der Velden wrote:
Please reply to list also.
Ccing list

Hi. Thanks. In fact my question is if postgres has software by which I 
can do distributed transactions, with 2pc. Normally I use narayana in 
wildfly for that, but has postgres also such software? In java they call 
it jta.


See here:

https://www.postgresql.org/docs/11/sql-prepare-transaction.html

I have never used it, so someone else will have to comment on actual usage.



Op di 2 apr. 2019 21:45 schreef Adrian Klaver >:


On 4/2/19 12:36 PM, Eric J. Van der Velden wrote:
 > On https://wiki.postgresql.org/wiki/2PC_on_FDW it says I have to
set the
 > configuration parameter "max_prepared_foreign_transactions"  in
 > postgresql.conf. When I restart postgres, I see:

  From what I see the above was a proposal that did not actually get
implemented, at least in the form above.

 >
 > LOG:  unrecognized configuration parameter
 > "max_prepared_foreign_transactions" in file

Which is true as I see no such GUC here:

https://www.postgresql.org/docs/11/runtime-config.html

 > "/var/lib/postgresql/data/postgresql.conf" line 689
 >
 > Thanks,
 > Eric.


-- 
Adrian Klaver

adrian.kla...@aklaver.com 




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: New LLVM JIT Features

[Thomas Munro]

On Wed, Apr 3, 2019 at 8:39 AM Praveen Velliengiri
 wrote:
> On Apr 2, 2019 12:51 AM, "preejackie"  wrote:
>> I'm Praveen Velliengiri, student from India. I'm working on developing a 
>> Speculative compilation support in LLVM ORC JIT Infrastructure.
>>
>> As LLVM ORC supports compiling in multiple backend threads, it would be 
>> effective if we compile the functions speculatively before they are called 
>> by the executing function. So when we request JIT to compile a function, JIT 
>> will immediately returns the function address for raw executable bits. This 
>> will greatly reduce the JIT latencies in modern multi-core machines. And 
>> also I'm working on designing a ORC in-place dynamic profiling support, by 
>> this JIT will automatically able to identify the hot functions, and compile 
>> it in higher optimization level to achieve good performance.
>>
>> I'm proposing this project for GSoC 2019. It would be helpful to know how 
>> this new features are effective to pgsql engine, so that I include your 
>> comments in "View from Clients" proposal section.

Hi Praveen,

FYI the final "commitfest" for PostgreSQL 12 is wrapping up right now
and the code freeze begins in a few days, so I wouldn't expect an
immediate reply.

-- 
Thomas Munro
https://enterprisedb.com

Re: New LLVM JIT Features

[Andres Freund]

Hi,

On 2019-04-02 00:51:51 +0530, preejackie wrote:
> As LLVM ORC supports compiling in multiple backend threads, it would be
> effective if we compile the functions speculatively before they are called
> by the executing function. So when we request JIT to compile a function, JIT
> will immediately returns the function address for raw executable bits. This
> will greatly reduce the JIT latencies in modern multi-core machines.

I personally think this should be approached somewhat differently -
putting patchpoints into code reduces the efficiency of the generated
code, so I don't think that's the right approach. What I think we should
do is to, if we decide it's worthwhile at plan time, generate the LLVM
IR time at the beginning of execution, but continue to use interpreted
execution initially. The generated IR would then be handed over to a
background [process|thread|whatnot] for optimization of code
generation. Then, when finished, I'd switch over from interpreted to JIT
compiled execution.  That approach will, in my view, yield better
latency behaviour because we can actually evaluate quals etc for which
we've not yet finished code generation.


> And also I'm working on designing a ORC in-place dynamic profiling support, by
> this JIT will automatically able to identify the hot functions, and compile
> it in higher optimization level to achieve good performance.

I think that's a nice concept, but at the moment the generated code is
so bad that it's much more likely to get big benefits by improving the
generated IR, compared to giving more hints to the optimizer.

Greetings,

Andres Freund

template0 is having high age of datforzenxid

[AI Rumman]

Hi,

I am running Postgresql 10 where I can see the template0 database is having
longest datfrozenxid:
```db_name  age_of_datfrozenxid
--
postgres251365
template1234574
template075366462
db1253097
db2250649
db3250649
db414641
db514214```

Initially I had high age for all the databases and I ran "vacuum freeze"
which brought down the age of other databases. But how can I do the same
for template0?
Please advise.

Thanks.

Re: template0 is having high age of datforzenxid

[Andres Freund]

Hi,

On 2019-04-02 15:35:53 -0700, AI Rumman wrote:
> I am running Postgresql 10 where I can see the template0 database is having
> longest datfrozenxid:
> ```db_name  age_of_datfrozenxid
> --
> postgres251365
> template1234574
> template075366462
> db1253097
> db2250649
> db3250649
> db414641
> db514214```
> 
> Initially I had high age for all the databases and I ran "vacuum freeze"
> which brought down the age of other databases. But how can I do the same
> for template0?

75 million isn't that high - autovacuum will automatically look at it
once above autovacuum_max_freeze_age (defaulting to 200 million).  If
you really for some reason need to make it have a lower datfrozenxid
(why?), you'd need to update its pg_database entry to allow connections,
connect to it, vacuum it, and change datallowconn back.

Greetings,

Andres Freund

Re: New LLVM JIT Features

[preejackie]

Hi Andres,

Thanks for the reply! Please see my comments inline.

On 03/04/19 3:20 AM, Andres Freund wrote:

Hi,

On 2019-04-02 00:51:51 +0530, preejackie wrote:

As LLVM ORC supports compiling in multiple backend threads, it would be
effective if we compile the functions speculatively before they are called
by the executing function. So when we request JIT to compile a function, JIT
will immediately returns the function address for raw executable bits. This
will greatly reduce the JIT latencies in modern multi-core machines.

I personally think this should be approached somewhat differently -
putting patchpoints into code reduces the efficiency of the generated
code, so I don't think that's the right approach. What I think we should
 What do you mean by patch points here? To my knowledge, LLVM symbols 
have arbitrary stub associated which resolve to function address at 
function address.

do is to, if we decide it's worthwhile at plan time, generate the LLVM
IR time at the beginning of execution, but continue to use interpreted
execution initially. The generated IR would then be handed over to a
background [process|thread|whatnot] for optimization of code
generation. Then, when finished, I'd switch over from interpreted to JIT
compiled execution.  That approach will, in my view, yield better
latency behaviour because we can actually evaluate quals etc for which
we've not yet finished code generation.



And also I'm working on designing a ORC in-place dynamic profiling support, by
this JIT will automatically able to identify the hot functions, and compile
it in higher optimization level to achieve good performance.

I think that's a nice concept, but at the moment the generated code is
so bad that it's much more likely to get big benefits by improving the
generated IR, compared to giving more hints to the optimizer.
By improving the generated IR, you mean by turning pgsql queries into 
LLVM IR? If it is the case, this design doesn't handles that, it works 
only when the given program representation is in LLVM IR.


Greetings,

Andres Freund


--
Have a great day!
PreeJackie

Re: New LLVM JIT Features

[Andres Freund]

On 2019-04-03 10:44:06 +0530, preejackie wrote:
> Hi Andres,
> 
> Thanks for the reply! Please see my comments inline.
> 
> On 03/04/19 3:20 AM, Andres Freund wrote:
> > Hi,
> > 
> > On 2019-04-02 00:51:51 +0530, preejackie wrote:
> > > As LLVM ORC supports compiling in multiple backend threads, it would be
> > > effective if we compile the functions speculatively before they are called
> > > by the executing function. So when we request JIT to compile a function, 
> > > JIT
> > > will immediately returns the function address for raw executable bits. 
> > > This
> > > will greatly reduce the JIT latencies in modern multi-core machines.
> > I personally think this should be approached somewhat differently -
> > putting patchpoints into code reduces the efficiency of the generated
> > code, so I don't think that's the right approach. What I think we should
>  What do you mean by patch points here? To my knowledge, LLVM symbols have
> arbitrary stub associated which resolve to function address at function
> address.

I was assuming that you'd want to improve latency by not compiling all
the functions at the start of the executor (like we currently do), but
have sub-functions compiled in the background. That'd require
patchpoints to be able to initially redirect to a function to wait for
compilation, which then can be changed to directly jump to the function.
Because we already just compile all the functions reachable at the start
of execution in one go, so it's not a one-by-one function affair.


> > do is to, if we decide it's worthwhile at plan time, generate the LLVM
> > IR time at the beginning of execution, but continue to use interpreted
> > execution initially. The generated IR would then be handed over to a
> > background [process|thread|whatnot] for optimization of code
> > generation. Then, when finished, I'd switch over from interpreted to JIT
> > compiled execution.  That approach will, in my view, yield better
> > latency behaviour because we can actually evaluate quals etc for which
> > we've not yet finished code generation.
> > 
> > 
> > > And also I'm working on designing a ORC in-place dynamic profiling 
> > > support, by
> > > this JIT will automatically able to identify the hot functions, and 
> > > compile
> > > it in higher optimization level to achieve good performance.
> > I think that's a nice concept, but at the moment the generated code is
> > so bad that it's much more likely to get big benefits by improving the
> > generated IR, compared to giving more hints to the optimizer.
> By improving the generated IR, you mean by turning pgsql queries into LLVM
> IR? If it is the case, this design doesn't handles that, it works only when
> the given program representation is in LLVM IR.

My point is that we generate IR that's hard for LLVM to optimize. And
that fixing that is going to give you way bigger wins than profile
guided optimization.

Greetings,

Andres Freund

Recommendation to run vacuum FULL in parallel

[Perumal Raj]

Hi ALL

We are  planning to reclaim unused space from 9.2 Version postgres Cluster,

Method : VACUUM FULL
DB Size : 500 GB
Expected space to reclaim 150 GB
work_mem : 250 MB
maintenance_work_mem : 20 GB

*Question :*

1. vacuumdb --j option (Parallel) not available for version 9.2.
  How to run vacuum full in parallel ? At present its taking 8Hrs if i run
sequential ( vacuum full verbose;)

2. If we run vacuum full, Do we need to run REINDEX/ANALYZE exclusively ?

3. What is the best way to run VACUUM FULL with less window.

Thanks,
Raj

Re: Recommendation to run vacuum FULL in parallel

[Ron]

On 4/3/19 12:50 AM, Perumal Raj wrote:

Hi ALL

We are  planning to reclaim unused space from 9.2 Version postgres Cluster,

Method : VACUUM FULL


Does *every* table have *so much* free space that it's impractical to just 
let the files just get refilled by normal usage?



DB Size : 500 GB
Expected space to reclaim 150 GB
work_mem : 250 MB
maintenance_work_mem : 20 GB

*Question :*

1. vacuumdb --j option (Parallel) not available for version 9.2.
  How to run vacuum full in parallel ? At present its taking 8Hrs if i run 
sequential ( vacuum full verbose;)


2. If we run vacuum full, Do we need to run REINDEX/ANALYZE exclusively ?

3. What is the best way to run VACUUM FULL with less window.


A good way to run *any* task like this in parallel is to generate X lists of 
objects, and then process each list in parallel.


--
Angular momentum makes the world go 'round.

Re: Recommendation to run vacuum FULL in parallel

[Laurenz Albe]

Perumal Raj wrote:
> We are  planning to reclaim unused space from 9.2 Version postgres Cluster,
> 
> Method : VACUUM FULL
> DB Size : 500 GB
> Expected space to reclaim 150 GB
> work_mem : 250 MB
> maintenance_work_mem : 20 GB
> 
> Question :
> 
> 1. vacuumdb --j option (Parallel) not available for version 9.2.
>   How to run vacuum full in parallel ? At present its taking 8Hrs if i run 
> sequential ( vacuum full verbose;)

Run several scripts in parallel, where each of them vacuums some bloated tables.
Be warned that VACUUM (FULL) is quite I/O intense, so too much parallelism
might overload your I/O system and harm performance.

> 2. If we run vacuum full, Do we need to run REINDEX/ANALYZE exclusively ?

You don't need to run REINDEX, because that happens automatically.
You can use VACUUM (FULL, ANALYZE) to also gather statistics.

> 3. What is the best way to run VACUUM FULL with less window.

Identify which tables really need it rather than VACUUMing everything.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com