Re: How to reply to an existing bug?

[Magnus Hagander]

On Mon, May 28, 2018, 02:00 Erwin Brandstetter  wrote:

> I found an existing bug report and have something to add to it.
>
> What's the best way to reply to it? Just using a browser, with no
> newsreader installed.
>
> This one:
>
> https://www.postgresql.org/message-id/flat/20170925084522.1442.32786%40wrigleys.postgresql.org#20170925084522.1442.32...@wrigleys.postgresql.org
>
>

There is no way to do that from the browser alone in the archives. The
easiest you can do is download the thread mbox (one is available for each
thread, so you don't have do download a whole month worth of email) and
open it in your mail program. That should give you an option to reply from
there, and the reply will properly have the references required to enter
the thread properly.

/Magnus

Re: Code of Conduct plan

[Magnus Hagander]

On Tue, Jun 5, 2018 at 4:45 PM, Chris Travers 
wrote:

>
>
> On Mon, Jun 4, 2018 at 6:59 PM, Joshua D. Drake 
> wrote:
>
>> On 06/03/2018 04:08 PM, Gavin Flower wrote:
>>
>> My comments:
>>>>
>>>> 1) Reiterate my contention that this is a solution is search of
>>>> problem. Still it looks like it is going forward, so see below.
>>>>
>>>> 2) "... engaging in behavior that may bring the PostgreSQL project into
>>>> disrepute, ..."
>>>> This to me is overly broad and pulls in actions that may happen outside
>>>> the community. Those if they are actually an issue should be handled where
>>>> they occur not here.
>>>>
>>>
>> This is good point. There are those who would think that one has
>> performed an action that brings the project into disrepute and a similar
>> sized bias that suggests that in fact that isn't the case. This based on
>> the CoC would be judged by the CoC committee.
>>
>> It is my hope that PostgreSQL.Org -Core chooses members for that
>> committee that are exceedingly diverse otherwise it is just an echo chamber
>> for a single ideology and that will destroy this community.
>
>
> If I may suggest:  The committee should be international as well and
> include people from around the world.  The last thing we want is for it to
> be dominated by people from one particular cultural viewpoint.
>

It will be. This is the PostgreSQL *global* development group and project,
after all. Yes, there is definitely a slant in the project in general
towards the US side, as is true in many other such projects, but in general
we have decent coverage of other cultures and countries as well. We can't
cover them all  on the committee (that would make for a gicantic
committee), but we can cover it with people who are used to communicating
and working with people from other areas as well, which makes for a better
understanding.

It won't be perfect in the first attempt, of course, but that one is
covered.

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

Re: Single query uses all system memory

[Magnus Hagander]

On Tue, Jun 26, 2018 at 11:40 AM, Ivar Fredriksen  wrote:

> A single large query is able to spend all the system memory (as reported
> by top), and the oom_reaper kills postgres. See bottom of email for an
> example query and logs.
>
>
>
> Expected behavior would be that postgres is not killed and the query is
> limited to the shared_buffer memory, potentially failing, but not killing
> the postgres process.
>

Then your expectations are completely wrong. shared_buffers have nothing to
do with limiting the memory of a query. shared_buffers set the size of the
PostgreSQL cache, not the working set. That's controlled by work_mem (see
below).



> Do I have some fundamental flaw in my understanding of this, or is there
> some sort of configuration that should be in place that is missing? The
> offending sample query has been
>
> rewritten to not use joins, and will no longer crash postgres. I am not
> asking anyone to spend a lot of time analyzing the query itself, it is just
> provided as an example for when the problem will occur.
>
> My question is more in a broader sense why one query is eating through all
> system memory, and is there anything I can do to prevent this from
> happening?
>
> We have set shared_buffers to 256MB on a test-system that has 1GB memory.
> Production machines have more resources, but the errors are happening in
> exactly the same way, so I assume (perhaps wrongly) that using the test
>
> system specs and logs might give me the answers I need to figure out what
> is happening.
>
>
>
> Technical details are provided below, a big thanks to anyone who can
> provide any help or insight to this.
>
>
>
> Regards,
>
> Ivar Fredriksen
>
>
>
> PostgreSQL version number:
>
> PostgreSQL 10.4 (Debian 10.4-2.pgdg90+1) on x86_64-pc-linux-gnu, compiled
> by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit
>
> Installed with the debian packages for postgres found at: deb
> http://apt.postgresql.org/pub/repos/apt/
>
>
>
> Changes made to the settings in the postgresql.conf file:
>
>  name | current_setting |
> source
>
> --+-
> +--
>
>  application_name | psql|
> client
>
>  checkpoint_completion_target | 0.9 |
> configuration file
>
>  client_encoding  | UTF8|
> client
>
>  cluster_name | 10/main |
> configuration file
>
>  DateStyle| ISO, MDY|
> configuration file
>
>  default_statistics_target| 100 |
> configuration file
>
>  default_text_search_config   | pg_catalog.english  |
> configuration file
>
>  dynamic_shared_memory_type   | posix   |
> configuration file
>
>  effective_cache_size | 1536MB  |
> configuration file
>
>  external_pid_file| /var/run/postgresql/10-main.pid |
> configuration file
>
>  lc_messages  | C   |
> configuration file
>
>  lc_monetary  | C   |
> configuration file
>
>  lc_numeric   | en_US.UTF-8 |
> configuration file
>
>  lc_time  | C   |
> configuration file
>
>  listen_addresses | *   |
> configuration file
>
>  log_line_prefix  | %m [%p] %q%u@%d |
> configuration file
>
>  log_timezone | UTC |
> configuration file
>
>  maintenance_work_mem | 128MB   |
> configuration file
>
>  max_connections  | 100 |
> configuration file
>
>  max_stack_depth  | 2MB |
> environment variable
>
>  max_wal_size | 2GB |
> configuration file
>
>  min_wal_size | 1GB |
> configuration file
>
>  port | 5432|
> configuration file
>
>  search_path  | "$user", public, pg_catalog |
> configuration file
>
>  shared_buffers   | 256MB   |
> configuration file
>
>  ssl  | on  |
> configuration file
>
>  ssl_cert_file| /etc/ssl/certs/ssl-cert-snakeoil.pem|
> configuration file
>
>  ssl_key_file | /etc/ssl/private/ssl-cert-snakeoil.key  |
> configuration file
>
>  standard_conforming_strings

Re: Code of Conduct committee: call for volunteers

[Magnus Hagander]

On Wed, Jun 27, 2018 at 11:44 AM, ERR ORR  wrote:

> [ clear attempt at trolling ]
>
>
This sort of verbiage has never been considered acceptable on the
PostgreSQL lists. Since the CoC is not yet in effect, it falls on the core
team to enforce community norms. Be advised that if you post something like
this again, you will be banned immediately and permanently from the lists.

-- 
Magnus Hagander
PostgreSQL Core Team

Re: correcting tablespaces inside data folder

[Magnus Hagander]

On Fri, Jul 6, 2018 at 6:42 PM, Duarte Carreira 
wrote:

> Hello.
>
> Yes I'm one of those guys who only recently realized the mess of having
> tablespaces inside the data directory... now I want to use pg_upgrade and
> it will make things even worse...
>
> Does anyone have a best approach to this problem? Fastest/safest?
>
> pg 9.3.x on win
>
>
If you can afford to shut the server down, the easiest is to shut it down,
move the tablespaces (with mv, and as long as you stay within the partition
it should be almost instant), update the symlinks to point to the new
location, and start it up again.

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

Re: correcting tablespaces inside data folder

[Magnus Hagander]

(please don't top-post. It makes it hard to follow discussions)

On Fri, Jul 6, 2018 at 7:05 PM, Duarte Carreira 
wrote:

> Magnus,
> You mean changing the symlinks inside pg_tblspc?
>
>
Yes. As long as the server is shut down, you can modify those symlinks.



>
> On Fri, Jul 6, 2018 at 5:49 PM Magnus Hagander 
> wrote:
>
>> On Fri, Jul 6, 2018 at 6:42 PM, Duarte Carreira 
>> wrote:
>>
>>> Hello.
>>>
>>> Yes I'm one of those guys who only recently realized the mess of having
>>> tablespaces inside the data directory... now I want to use pg_upgrade and
>>> it will make things even worse...
>>>
>>> Does anyone have a best approach to this problem? Fastest/safest?
>>>
>>> pg 9.3.x on win
>>>
>>>
>> If you can afford to shut the server down, the easiest is to shut it
>> down, move the tablespaces (with mv, and as long as you stay within the
>> partition it should be almost instant), update the symlinks to point to the
>> new location, and start it up again.
>>
>> --
>>  Magnus Hagander
>>  Me: https://www.hagander.net/ <http://www.hagander.net/>
>>  Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>
>>
>


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

Re: PG backup check

[Magnus Hagander]

On Tue, Jul 17, 2018 at 11:17 AM, Guillaume Lelarge 
wrote:

> 2018-07-17 2:35 GMT+02:00 Ravi Krishna :
>
>> Not sure I am following this.  Did Google release this because PG backups
>> are not 100% reliable or the data corruption can occur due to hardware
>> failure.
>>
>> http://www.eweek.com/cloud/google-releases-open-source-tool-
>> that-checks-postgres-backup-integrity?utm_medium=email&
>> utm_campaign=EWK_NL_EP_20180713_STR5L2&dni=450493554&rni=24844166
>>
>
> From what I understand with this Google tool, it has nothing to do with
> backups. It just allows you to check data blocks in a PostgreSQL cluster.
> Google advice is to run it before taking a backup, but that's about it.
>
>
This appears to basically be the same tool that's already included in
PostgreSQL 11, and has been around in a few different incarnations (but
unpolished) for years.

FWIW, in relation to backups, tools like pgbackrest already did this
transparently during backup, and again PostgreSQL 11 will do it built-in.

It's quite possible Google was running this internally before of course,
and a separate tool from others, but it's not exactly news...  But they do
outline a very definite problem, which is that if you get physical
corruption in your database, it gets included in the backups. And if it's
in a portion of the database you don't use a lot, checksum failures won't
be noticed until you actually try, which is way too late.

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

Re: User documentation vs Official Docs

[Magnus Hagander]

On Tue, Jul 17, 2018 at 1:47 PM, Peter Eisentraut <
peter.eisentr...@2ndquadrant.com> wrote:

> On 17.07.18 02:13, Joshua D. Drake wrote:
> > On 07/16/2018 05:08 PM, Alvaro Herrera wrote:
> >>
> >> Sounds like wiki pages could solve need this pretty conveniently.  If
> >> and when the content is mature enough and migrates to the tutorial main
> >> documentation pages, the wiki pages can be replaced with redirects to
> >> those.
> >
> > Anyone who writes a lot is going to rebel against using a wiki. They are
> > one of the worst to write in from a productivity perspective. I would
> > rather write in Docbook, at least then I can template everything and we
> > could have a standard xsl sheet etc...
>
> I don't really buy that.  The wiki seems just fine for writing short to
> medium size how-to type articles.  We already have good content of that
> sort in the wiki right now.  It's not like there isn't going to be
> anyone who will rebel against any of the other tool chains that have
> been mentioned.
>

I think the biggest problem with the wiki for that type of content has
nothing to do with the formatting, and everything to do with the structure.
By definition the wiki is unstructured. One could put a structure on top of
it, with proper categories and indexing pages. That's done for some info on
it, but not for all. There's also a lot of outdated information.

Both those things are things that could be solved by somebody with the time
and willingness to trawl through the wiki to update such things, and then
to keep things updated. But keeping it updated is an equal amount of work
regardless of platform. If we find somebody who wants to do that, then at
least *starting out* on the wiki is a good idea. It's usually a Good Enough
(TM) system. And the most common things I see people writing such things in
today are Markdown (hi github!) or RST (hi Sphinx!) anyway, both of which
are pretty similar to the wiki markup. Which means that the project could
*start out* using the wiki, and once there is enough content to prove the
idea other platforms could be looked at and it would be easy enough to
migrate that data out there (even if just by copy/paste) if it becomes a
need.

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

Re: pg_basebackup exit codes

[Magnus Hagander]

On Mon, Aug 19, 2019 at 12:18 PM John Donath  wrote:

> Hi,
>
>
>
> I was unpleasantly surprised to see that pg_basebackup will return a zero
> (Success) exit code even when it has actually failed like below:
>
>
>
> pg_basebackup: could not write to compressed file
> "/var/lib/pgsql/10/inst1/backup/cust-pg02_inst1_Fri_20190816/base.tar.gz":
> No space left on device
>
> pg_basebackup: removing data directory
> "/var/lib/pgsql/10/inst1/backup/cust-pg02_inst1_Fri_20190816"
>
>
>
> A reliable exit code is of great importance in backup scripts using a
> statement like below:
>
>
>
> pg_basebackup -D ${backup_dest} -Ft -z 2>&1 | tee -a ${log_file}; rv=$?
>
> [ $rv -eq 0 ] || error "pg_basebackup failed with rcode $rv; also see
> ${log_file}"
>
>
>
> Pg_basebackup version: (PostgreSQL) 10.10
>
>
>
> Anybody else encountering this problem?
>
>
>

I think your problem is that you are looking at the exit code from "tee"
and not from pg_basebackup. If you are using bash, you can look at
something like $PIPESTATUS to get the exit code from the actual comman
dbefore the pipe. Or you can try removing the tee command to verify if this
is indeed where the problem is coming from.


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

Re: Security patch older releases

[Magnus Hagander]

 Thu, Aug 29, 2019 at 2:05 PM Erika Knihti-Van Driessche <
erika.kni...@gmail.com> wrote:

> Hi,
>
> I think I've seen this question pass by earlier, but don't immediately
> find it back..
>
> So, I have postgres 9.6.9 and customer wants it to be updated to 9.6.11,
> because that is their current testing "sandbox" version, which was not
> installed by me and is also another linux distro.
>
> Now, the newest security update is 9.6.15.. If I download that RHEL
> repository, I will automatically get that version, and won't be able to
> choose the exact patch..
>
> So my question is, is there a way to get the 9.6.11 update?
>
>
PostgreSQL does not release individual security patches. The way to get the
security patch is to install the latest minor version, see
https://www.postgresql.org/support/security/.

If you want to get an individual security patch you will have to cherry
pick it from git and build your own server from source. But per the above
link, it is really recommended that you don't do that. Instead, do it the
way it's intended to, which means install the latest minor release.

Why would you not want the other security patches, or other important
bugfixes?

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

Re: SSPI auth and mixed case usernames

[Magnus Hagander]

On Fri, Aug 30, 2019 at 1:27 PM Niels Jespersen  wrote:

> Hello
>
> Postgresql 11.2 on Windows.
>
> I have a user mapping i pg_hba.conf
>
> sspi map=domain
>
> In pg_ident.conf, I have the following:
>
> domain/^(([A-Z|a-z]{3}[A|a]?)|([Xx]\d{2}))@DOMAIN$\1
>
> This maps windows logonname til a postgres username. Hower, for reasons I
> cannot explain, sometimes the username comes in all-lowercase, at other
> times it comes all-caps. This is dependant on the Windows host the client
> is connected to.
>

It is actually dependent on what the user typed into their login box when
they logged in to the machine. Yes, that's mostly insane, but that's how
those APIs in Windows work.


I do not want to create both XXX and xxx as users on Postgres. I would
> prefer to translate alle usernames to lowercase in the map.
>
> Is that possible, and if so, how?
>

No, PostgreSQL will not do that automatically for you.

What pg_ident.conf allows you to do is say that the user is allowed to log
in to the postgres user in lowercase even if the username retrieved using
sspi is not in lowercase. But the application still has to actually try to
log in with lowercase, and do so before it connects to PostgreSQL.

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

Re: SSPI auth and mixed case usernames

[Magnus Hagander]

On Fri, Aug 30, 2019 at 3:00 PM Niels Jespersen  wrote:

> Hello Magnus
>
> Thank you for your prompt reply.
>
> I’m not sure I understand your last statement. I want to achieve that
> regardless of the case of the entered username is logged into the same
> Postgres user (whose name is created in all lowercase).
>
> In other words, Windows usernames one day entered as XYz, the next day
> entered as xYz, should logon to Postgres user xyz.
>
>
Right. The client application needs to enforce that the usernamed passed to
PostgreSQL is lowercased before it tries to connect. The only thing
pg_ident mapping does is make sure that it actually works  when the client
application does this, but it's the client that has to do it.

For example, if using psql you can't say "psql -h myserver.domain.com -d
mydatabase", you have to explicitly say "psql -h myserver.domain.com -d
mydatabase -U mylowercaseusername"

//Magnus

Re: PG12

[Magnus Hagander]

On Thu, Oct 3, 2019 at 6:31 PM Adrian Klaver 
wrote:

> On 10/3/19 9:27 AM, Igor Neyman wrote:
> > Main page (https://www.postgresql.org/) announces new release, but
> > Downloads for Windows page
> > (https://www.postgresql.org/download/windows/) doesn’t list PG12.
> >
> > Any clarification?
>
> It is available:
>
> https://www.enterprisedb.com/downloads/postgres-postgresql-downloads
>
> Just looks like the community page has not been updated to reflect
>

That's the list of platforms that EnterpriseDB have tested their installers
on. So we obviously have to wait for EDB to provide updated lists there --
but until then, you can probably expect it to work just fine.

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

Re: Version 10.7 of postgres

[Magnus Hagander]

On Thu, Oct 10, 2019 at 6:52 AM Shankar Bhaskaran 
wrote:

> Hi ,
>
> We are planning to use postgres 10.7 version as that is the latest
> version supported on Aurora DB. Since we have an on premise installation
> also , i was trying to download the same version of postgres for windows
> and linux.
> Unfortunately that version is not available in the download site as well
> as the ftp site .Is there any reason why an installer for 10.7 version in
> windows and linux is not added to archives?
>
>
Note that Aurora is not PostgreSQL, so you cannot compare versions
straight. If you want an on premise installation that's identical to the
one in the cloud, you should use PostgreSQL on both sides.

You should be using 10.10, as it has important bug fixes including security
fixes. Whether Aurora contains those fixes or not you need to check with
the vendor, but if not you should be complaining to that vendor that they
are missing important fixes, rather than try to install a version with
known bugs and security issues.

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

Re: Help with authentication on Debain/Ubuntu installation

[Magnus Hagander]

On Thu, Nov 21, 2019 at 1:46 PM stan  wrote:

> I am trying to set up to do some work with pg_dump, and I would like to be
> able to connect from my normal user to do this. This is on a Ubunt 18.04
> installation. I have added the follwing to pg_hba.conf:
>
> hostall all 0.0.0.0/0   md5
> hostssl all all 0.0.0.0/0   md5
>
> But when I try to login like this:
>
> psql stan postgres -W
>
> I get the following error, and yes, I set postgres'es password to something
> I know:
>
> tan@stantest:~$ psql stan postgres -W
> Password:
> psql: error: could not connect to server: FATAL:  Peer authentication
> failed for user "postgres"
> stan@stantest:~$
>
> What am I doing wrong?
>
>
By default, Debian has a row in pg_hba.conf that enforces peer specifically
for the postgres superuser when connecting over unix sockets (which is a
very good thing from a security perspective). You will have to find that
row further up in the file and comment it out (since the first row that
matches will be used).

//Magnus

Re: archiving question

[Magnus Hagander]

On Fri, Dec 6, 2019 at 10:50 AM Zwettler Markus (OIZ) <
markus.zwett...@zuerich.ch> wrote:

> > -Ursprüngliche Nachricht-
> > Von: Michael Paquier 
> > Gesendet: Freitag, 6. Dezember 2019 02:43
> > An: Zwettler Markus (OIZ) 
> > Cc: Stephen Frost ;
> pgsql-general@lists.postgresql.org
> > Betreff: Re: archiving question
> >
> > On Thu, Dec 05, 2019 at 03:04:55PM +, Zwettler Markus (OIZ) wrote:
> > > What do you mean hear?
> > >
> > > Afaik, Postgres runs the archive_command per log, means log by log by
> log.
> > >
> > > How should we parallelize this?
> >
> > You can, in theory, skip the archiving for a couple of segments and then
> do the
> > operation at once without the need to patch Postgres.
> > --
> > Michael
>
>
> Sorry, I am still confused.
>
> Do you mean I should move (mv * /backup_dir) the whole pg_xlog directory
> away and move it back (mv /backup_dir/* /pg_xlog) in case of recovery?
>
>
No, *absolutely* not.

What you can do is have archive_command copy things one by one to a local
directory (still sequentially), and then you can have a separate process
that sends these to the archive -- and *this* process can be parallelized.

//Magnus

Re: archiving question

[Magnus Hagander]

On Fri, Dec 6, 2019 at 12:06 PM Zwettler Markus (OIZ) <
markus.zwett...@zuerich.ch> wrote:

> > On Fri, Dec 6, 2019 at 10:50 AM Zwettler Markus (OIZ)  markus.zwett...@zuerich.ch> wrote:
> >> -Ursprüngliche Nachricht-
> >> Von: Michael Paquier <mailto:mich...@paquier.xyz>
> >> Gesendet: Freitag, 6. Dezember 2019 02:43
> >> An: Zwettler Markus (OIZ) <mailto:markus.zwett...@zuerich.ch>
> >> Cc: Stephen Frost <mailto:sfr...@snowman.net>; mailto:
> pgsql-general@lists.postgresql.org
> >> Betreff: Re: archiving question
> >>
> >> On Thu, Dec 05, 2019 at 03:04:55PM +, Zwettler Markus (OIZ) wrote:
> >> > What do you mean hear?
> >> >
> >> > Afaik, Postgres runs the archive_command per log, means log by log by
> log.
> >> >
> >> > How should we parallelize this?
> >>
> >> You can, in theory, skip the archiving for a couple of segments and
> then do the
> >> operation at once without the need to patch Postgres.
> >> --
> >> Michael
> >
> >
> >Sorry, I am still confused.
> >
> >Do you mean I should move (mv * /backup_dir) the whole pg_xlog directory
> away and move it back (mv /backup_dir/* /pg_xlog) in case of recovery?
> >
> >No, *absolutely* not.
> >
> >What you can do is have archive_command copy things one by one to a local
> directory (still sequentially), and then you can have a separate process
> that sends these to the archive -- and *this* process can be parallelized.
> >
> >//Magnus
>
>
>
> That has been my initial question.
>
> Is there a way to tune this sequential archive_command log by log copy in
> case I have tons of logs within the pg_xlog directory?
>

It will be called one by one, there is no changing that. What you *do* with
that command is up to you, so you can certainly tune that. But as soon as
your command has returned PostgreSQL wil lhave the "right" to remove the
file if it thinks it's time. But you could for example have a daemon that
opens a file handle to the file in response to your archive command thereby
preventing it from actually being removed, and then archives them in
private, in which case the archiving only has to wait for it to acknowledge
the process has started, not finished.

There's always a risk involved in returning from archive_command before the
file is safely stored on a different machine/storage somewhere. The more
async you make it the bigger that risk is, but it increases your ability to
parallelize.

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

Re: Inexplicable duplicate rows with unique constraint

[Magnus Hagander]

On Thu, Jan 16, 2020 at 6:08 PM Tom Lane  wrote:
>
> Richard van der Hoff  writes:
> > I'm trying to track down the cause of some duplicate rows in a table
> > which I would expect to be impossible due to a unique constraint. I'm
> > hoping that somebody here will be able to suggest something I might have
> > missed.
>
> Since these are text columns, one possibility you should be looking into
> is that the indexes have become corrupt due to a change in the operating
> system's sorting rules for the underlying locale.  I don't recall details
> at the moment, but I do remember that a recent glibc update changed the
> sorting rules for some popular locale settings.  If an installation had
> applied such an update underneath an existing database, you'd have a
> situation where existing entries in an index are not in-order according
> to the new behavior of the text comparison operators, leading to havoc
> because btree searching relies on the entries being correctly sorted.

See https://wiki.postgresql.org/wiki/Locale_data_changes for hints on
which linux distros updated when.

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

Re: Memory usage of pg_basebackup...

[Magnus Hagander]

On Wed, Jan 22, 2020 at 11:23 AM Mladen Marinović
 wrote:
>
> Hi,
>
> Does anybody know how much RAM does pg_basebackup use and if it depends on 
> the size of the database that the backup is being created?

pg_basebackup uses very little RAM, as the data received is written to
disk immediately. If you are doing compression it uses a little more,
but still within the "you don't really need to care" range. The RAM
usage is not dependent on the size of the database, it is always that
small.

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

Re: Using of --data-checksums

[Magnus Hagander]

On Sun, Apr 12, 2020 at 8:05 AM Michael Paquier  wrote:

> On Fri, Apr 10, 2020 at 04:37:46PM -0400, Stephen Frost wrote:
> > There's definitely a lot of reasons to want to have the ability to
> > change an existing cluster.  Considering the complications around
> > running pg_upgrade already, I don't really think that changing the
> > default of initdb would be that big a hurdle for folks to deal with-
> > they'd try the pg_upgrade, get a very quick error that the new cluster
> > has checksums enabled and the old one didn't, and they'd re-initdb the
> > new cluster and then re-run pg_upgrade to figure out what the next issue
> > is..
>
> We discussed that a couple of months ago, and we decided to keep that
> out of the upgrade story, no?  Anyway, if you want to enable or
> disable data checksums on an existing cluster, you always have the
> possibility to use pg_checksums --enable.  This exists in core since
> 12, and there is also a version on out of core for older versions of
> Postgres: https://github.com/credativ/pg_checksums.  On apt-based
> distributions like Debian, this stuff is under the package
> postgresql-12-pg-checksums.
>

The fact that this tool exists, and then in the format of pg_checksums
--disable, I think is what makes the argument to turn on checksums by
default possible. Because it's now very easy and fast to turn it off even
if you've accumulated sizable data in your cluster. (Turning it on in this
case is easy, but not fast).

And FWIW, I do think we should change the default. And maybe spend some
extra effort on the message coming out of pg_upgrade in this case to make
it clear to people what their options are and exactly what to do.

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

Re: timestamp and timestamptz

[Magnus Hagander]

On Wed, Apr 15, 2020 at 7:50 PM Niels Jespersen  wrote:

> Hello all
>
>
>
> We have some data that have entered a timestamp column from a csv. The
> data in the csv are in utc. We want to access the data in our native
> timezone (CET).
>
>
>
> I am considering a few alternatives:
>
>
>
> 1.   Early in the process, convert to timestamptz and keep this
> datatype.
>
> 2.   Early in the process, convert to timestamp as understood in
> CET.  This will imply by convention that the data in the timestamp column
> represents CET. Users will need to be told that data represents CET, even
> if data is somwhere in the future kept in another country in another
> timezone.
>
>
>
> I probably should choose 1 over 2. But I am a bit hesitant, probably
> because we almost never have used timestamptz.
>

Yes, you should.



> Can we agree that the below query is selecting both the original utc
> timestamps and 2 and 1 (as decribed above)?
>
>
>
> set timezone to 'cet';
>
> select read_time read_time_utc, (read_time at time zone 'utc')::timestamp
> read_time_cet, (read_time at time zone 'utc')::timestamptz read_time_tz
> from t limit 10;
>
>
>
As long as you use option 1:

SELECT read_time
will return the time in CET (as a timestamptz) after you've set timezone to
'cet'. If you set timezone to 'utc' it will directly return utc.

SELECT read_time AT TIME ZONE 'utc'
will return the time in UTC (as a timestamp)


And just make sure you have done a "set time zone 'utc'" before you *load*
the data, and everything should just work automatically.

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

Re: timestamp and timestamptz

[Magnus Hagander]

On Thu, Apr 16, 2020 at 6:12 AM Niels Jespersen  wrote:

>
>
>
>
> *Fra:* Magnus Hagander 
> *Sendt:* 15. april 2020 20:05
> *Til:* Niels Jespersen 
> *Cc:* pgsql-general@lists.postgresql.org
> *Emne:* Re: timestamp and timestamptz
>
>
>
>
>
>
>
> On Wed, Apr 15, 2020 at 7:50 PM Niels Jespersen  wrote:
>
> Hello all
>
>
>
> We have some data that have entered a timestamp column from a csv. The
> data in the csv are in utc. We want to access the data in our native
> timezone (CET).
>
>
>
> I am considering a few alternatives:
>
>
>
> 1.   Early in the process, convert to timestamptz and keep this
> datatype.
>
> 2.   Early in the process, convert to timestamp as understood in
> CET.  This will imply by convention that the data in the timestamp column
> represents CET. Users will need to be told that data represents CET, even
> if data is somwhere in the future kept in another country in another
> timezone.
>
>
>
> I probably should choose 1 over 2. But I am a bit hesitant, probably
> because we almost never have used timestamptz.
>
>
>
> Yes, you should.
>
>
>
>
>
> Can we agree that the below query is selecting both the original utc
> timestamps and 2 and 1 (as decribed above)?
>
>
>
> set timezone to 'cet';
>
> select read_time read_time_utc, (read_time at time zone 'utc')::timestamp
> read_time_cet, (read_time at time zone 'utc')::timestamptz read_time_tz
> from t limit 10;
>
>
>
>
>
> As long as you use option 1:
>
>
>
> SELECT read_time
>
> will return the time in CET (as a timestamptz) after you've set timezone
> to 'cet'. If you set timezone to 'utc' it will directly return utc.
>
>
>
> SELECT read_time AT TIME ZONE 'utc'
>
> will return the time in UTC (as a timestamp)
>
>
>
>
>
> And just make sure you have done a "set time zone 'utc'" before you *load*
> the data, and everything should just work automatically.
>
>
>
> --
>
>  Magnus Hagander
>  Me: https://www.hagander.net/ <http://www.hagander.net/>
>  Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>
>
>
>
>
>
> Thank you Magnus (and others) for your replies.
>
>
>
> The raw input data are in this, slightly strange format: 2019.05.01
> 00:00:00. No timezone indicator, just an informal guarantee from the
> supplier that it is indeed utc. And no real chance of changing the format.
> We know, from experience.
>
>
>
> The data volume is a bit high, a few billion rows pr month. So, table
> partitioning is very helpful (aka really indispensable). Data will be
> aggregated in several ways for analytics. Time aggregations must be
> according to our local timezone (cet). We do not want data from one day
> being aggregated into the wrong date because of timezone issues. This means
> that partition boundaries (monthly pratitions most often, sometimes day
> partitions) must be on CET-boundaries so that partition pruning will pull
> data from the relevant cet-month not the utc-month.
>
>
>
> Now, if I load data into a timestamptz with timezone set to utc, partition
> to cet-boundaries, query and aggredate with timezone set to cet, everything
> wil be ok, I think. My small testcase below shows that the row goes into
> the april-partition (as it should). The planner does the correct partition
> pruning according to specified filtering and set timezone. All good.
>
>
>
> create table t (t_id bigserial, ts timestamptz) partition by range (ts);
>
> create table t_2020_02 partition of t for values from ('2020-02-01
> 00:00:00+01') to ('2020-03-01 00:00:00+01');
>
> create table t_2020_03 partition of t for values from ('2020-03-01
> 00:00:00+01') to ('2020-04-01 00:00:00+02');
>
> create table t_2020_04 partition of t for values from ('2020-04-01
> 00:00:00+02') to ('2020-05-01 00:00:00+02');
>
>
>
> set timezone to 'utc';
>
> insert into t (ts) values('2020-03-31 23:30:00');
>
>
>
> Once again, thank you for invaluable feedback.
>
>
>

Yes, this should work just fine. The internal representation of timestamptz
is always UTC, and it's only converted on entry/exit.

You can see this clearly if you create your partitions like above, and then
do a "set timezone to 'America/Los_Angeles'" followed by \d+ t in psql.
This will now show you what the partition bounds are in that timezone.

You can also just specify the timestamps when you create your partition
without including the timezone (+01) specifier. In this PostgreSQL will
interpret it as whatever your current value for the timezone setting is, so
as long as it's CET it should work fine, and you don't have to remember
which months are in DST and which are not.


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

Re: Using of --data-checksums

[Magnus Hagander]

On Sun, Apr 12, 2020 at 4:23 PM Tom Lane  wrote:

> Magnus Hagander  writes:
> > And FWIW, I do think we should change the default. And maybe spend some
> > extra effort on the message coming out of pg_upgrade in this case to make
> > it clear to people what their options are and exactly what to do.
>
> Is there any hard evidence of checksums catching problems at all?
> Let alone in sufficient number to make them be on-by-default?
>

I would say yes. I've certainly had a fair number of cases where they've
detected storage corruption, especially with larger SAN type installation.
And coupled with validating the checksum on backup (either with
pg_basebackup or pgbackrest) it enables you to find the errors *early*,
while you can still restore a previous backup and replay WAL to get to a
point where you don't have to lose any data.

I believe both Stephen and David have some good stories they've heard from
people catching such issues with backrest as well.

This and as Michael also points out, it lets you know that the problem
occurred outside of PostgreSQL, makes for very important information when
tracking down issues.

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

Re: Binary downloads and upgrading the host OS

[Magnus Hagander]

On Sat, Apr 25, 2020 at 5:39 PM Tom Browder  wrote:

> On Sat, Apr 25, 2020 at 10:24 AM Adrian Klaver
>  wrote:
> > On 4/25/20 8:04 AM, Tom Browder wrote:
> > > I am using the PostgreSQL binary downloads for Debian from your apt
> > > repositories.
> ...
> > Looks good to me. I'm assuming you are leaving the Postgres version the
> > same though. Is that correct?
>
> Yes, I already use the latest stable via the apt setup (12 I think).
>
> > Also, if it is not already planned, to be on the safe side throw in a
> > backup of some sort just before shutting down the server.
>
> Yes indeed!
>

Also don't forget that on an upgrade from Debian 9 to 10, you most likely
need to reindex your string indexes, see
https://wiki.postgresql.org/wiki/Locale_data_changes
<https://wiki.postgresql.org/wiki/Locale_data_changes#Debian>.

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

Re: PostgreSQL Server version compatibility check with Window 2016

[Magnus Hagander]

Hello!

This address is for reporting issues with the PostgreSQL website, it is not
a help desk. For general support questions, please see
https://www.postgresql.org/support/ - in particular, consider the
pgsql-general public mailinglist, as you seem to have already done, and
received a reply there (though it was two years ago).

You should also note that 9.6.7 or 9.6.9 is not supported on any platform,
you need to run 9.6.17 if you run a 9.6 version. This is why there is no
download link to an unsupported 9.6.9 version.

And as stated on the Windows download page at
https://www.postgresql.org/download/windows/, you need at least version 10
to have one that's tested on Windows 2016. But as the page also says, "They
can generally be expected to run on other comparable versions".


//Magnus


On Mon, Apr 27, 2020 at 11:31 AM  wrote:

>
>
> Hello Postgress Help Desk,
>
>
>
> Hope you are doing well.
>
>
>
> We are running our Qlik Sense application with postgress 9.6.7 version  &
> window 2016 server.
>
>
>
> We have often getting environment related issues and Qlik sense vendor
> confirmed that postgress was not supported with windows 2016.
>
>
>
> We understand from the below link, you didn’t test completely postgress
> 9.6.9 with windows 2016.
>
>
>
>
> https://www.postgresql.org/message-id/f7b9eff1d97e4eabb7cee9c497c5b3a8%40CTSINCHNSXCM011.cts.com
> 
>
>
>
> Kindly check above link and let us know your recommendations .
>
>
>
> Note: Qlik Sense server(Sep 2019 version) will support postgress 9.6.x
> version only so we are looking PostgreSQL  9.6.9 compatibility check with
> windows 2016
>
>
>
> We have noticed from postgress official  download page , we couldn’t able
> to see PostgreSQL 9.6.9 version download link.
>
>
>
>
>
> Reference link:
> https://www.enterprisedb.com/downloads/postgres-postgresql-downloads
> 
>
>
>
>
>
>
>
>
> Can you please let us know recommendation on this?
>
>
>
>
>
> Regards,
>
> Balaji
>
> Agilent AMS – BI Qlik Sense
>
> Phone: +91-9790034281
>
> Qlik PDL: *pdlctsbiqlikse...@cognizant.com*
> 
>
> Cognizant Technology Solutions.
>
> *Escalation Contact: **megavannan.kanniap...@cognizant.com*
> 
>
> *Escalation Matrix* - *https://spark.it.agilent.com/docs/DOC-58251*
> 
>
> *Please cc: Support PDL in your reply for optimal support coverage*
>
>
>
> *From:* M, Balaji (Cognizant)
> *Sent:* Monday, April 13, 2020 9:27 PM
> *To:* pgsql-general@lists.postgresql.org
> *Cc:* C, Praveen kumar (Cognizant) 
> *Subject:* PostgreSQL Server version compatibility check with Window 2016
>
>
>
> Hello Postgress Help Desk,
>
>
>
> Hope you are doing well.
>
>
>
> We are running our Qlik Sense application with postgress 9.6.7 version  &
> window 2016 server.
>
>
>
> We have often getting environment related issues and Qlik sense vendor
> confirmed that postgress was not supported with windows 2016.
>
>
>
> We understand from the below link, you didn’t test completely postgress
> 9.6.9 with windows 2016.
>
>
>
>
> https://www.postgresql.org/message-id/f7b9eff1d97e4eabb7cee9c497c5b3a8%40CTSINCHNSXCM011.cts.com
> 
>
>
>
> Kindly check above link and let us know your recommendations .
>
>
>
> Note: Qlik Sense server(Sep 2019 version) will support postgress 9.6.x
> version only so we are looking PostgreSQL  9.6.9 compatibility check with
> windows 2016
>
>
>
> We have noticed from postgress official  download page , we couldn’t able
> to see PostgreSQL 9.6.9 version download link.
>
>
>
>
>
> Reference link:
> https://www.enterprisedb.com/downloads/postgres-postgresql-downloads
> 

Re: Lock Postgres account after X number of failed logins?

[Magnus Hagander]

On Wed, May 6, 2020 at 5:26 PM Peter J. Holzer  wrote:

> On 2020-05-06 09:28:28 -0400, Stephen Frost wrote:
> > LDAP-based authentication in PG involves passing the user's password to
> > the database server in the clear (or tunneled through SSL, but that
> > doesn't help if the DB is compromised), so it's really not a good
> > solution.
>
> Still a lot better than PostgreSQL's md5 scheme, which stores
> password-equivalent hashes: If the database is compromised the attacker
> has all hashes immediately and can use them to login. Intercepting
> encrypted traffic even at the endpoint is much harder and can only
> uncover passwords actually used.
>

If the database is compromised the attacker already has the data, though,
so not as many needs to log in anymore.

But more to the point -- one should not use md5 in PostgreSQL these days,
one should be using scram-sha-256 which does not have this problem (and has
been around for a few years by now)., if using local database logins.

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

Re: Best way to use trigger to email a report ?

[Magnus Hagander]

On Fri, May 8, 2020 at 8:05 PM David G. Johnston 
wrote:

> On Fri, May 8, 2020 at 10:19 AM Christophe Pettus 
> wrote:
>
>> If you don't want to periodically poll the table, you can use NOTIFY
>> within the trigger to wake up a process that is waiting on NOTIFY.
>>
>
> Kinda.
>
> "With the libpq library, the application issues LISTEN as an ordinary SQL
> command, and then must periodically call the function PQnotifies to find
> out whether any notification events have been received.".
>
> IOW, the interface for the client is still a polling interface its just
> that with LISTEN the event is transient and in-memory only (on the server)
> and thus has less overhead.
>
>
Doesn't have to be. You can use PQsocket() to get the socket back, and then
select() or poll() on that socket, and you only have to call PQnotifies()
once that call has indicated "something happened".

Similar applies to writing such daemons using for example the python or
perl interfaces. You block your process or thread on select() and take
action when that one returns.

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

Re: Clarification related to BDR

[Magnus Hagander]

On Thu, May 14, 2020 at 9:01 AM Santhosh Kumar 
wrote:

> Hi,
>  I came across a link published in postgresql, where it is clearly
> mentioned BDR as an open source. When I tried to install BDR for CentOS
> from 2ndQuadrant, the yum repository was not reachable and upon further
> enquiring with 2ndQuadrant, I got a reply from them quoting as follows
>
> "BDR is not open source. We do not have plans to open source this."
>
>  Can you please help me understand, why the following news is published in
> "postgresql" with an encouraging message acknowledging BDR as an open
> source? We invested time and effort to use BDR only to understand at a
> later point in time, that it is not. Kindly clarify, if I am missing
> anything.
>
> https://www.postgresql.org/about/news/1689/
>
> [image: image.png]
>
>
>
This news is from 2016. At that time, BDR was open source, but it has since
been closed.

//Magnus

Re: How to start slave after pg_basebackup. Why min_wal_size and wal_keep_segments are duplicated

[Magnus Hagander]

On Mon, Jun 1, 2020 at 10:17 AM Andrus  wrote:

> Hi!
>
> > I have tried to re-initiate replica serveral times in low-use time but
> this error occurs again.
> >remove the whole replica's PGDATA/* and do a pg_basebackup again. But
> before that, make sure wal_keep_segments in big enough on the
> >master and,
>
> I renamed whole cluster before pg_basebackup
>
> >just as much important, do a vacuumdb -a (takes much space during the
> process) and use archiving!
>
> I run vacuumdb --full --all before pg_basebackup
>
> > If named replication slot is used commands like
> > vacuumdb --all --full
> > will cause main server crash due to disk space limit. pg_wal directory
> will occupy free disk space. After that main server stops.
> >>if you have disk constraints you will run into trouble sooner or later
> anyway. Make sure, you have enough disk space. There's no
> >>way around that anyway.
>
> This space is sufficient for base backup and replication.
>
> >> I tried using wal_keep_segments =180
> >> Will setting wal_keep_segments to higher value allw replication start
> after pg_basebackup ?
> >it depends. If you start the replica immediately and don't wait for hours
> or days, you should be good to go. But that depends on
> >different factors, for example, how >many WAL files are written during
> the pg_basebackup and pg_ctl start of the replica. If more
> >than 180 WALs have gone by on the master because it is really busy, >then
> you're probably lost again. Point being, you'll have to
> >launch the replica before WALs are expired!
> >Again: Make sure you have enough disk space, use archiving and use a
> replication slot.
>
> I tried with wal_keep_segments=360 but problem persisists.
> Server generates lot of less than 300 wal files.
>

Have you verified that wal_keep_segments actually end up at 360, by
connecting to the database and issuing SHOW wal_keep_segments? I've seen
far too many examples of people who accidentally had a second line that
overrode the one they thought they changed, and thus still ran with a lower
number.


Shell script starts server after pg_basebackup completes automatically:
>
> PGHOST=example.com
> PGPASSWORD=mypass
> PGUSER=replikaator
> export PGHOST  PGPASSWORD PGUSER
> /etc/init.d/postgresql stop
> mv /var/lib/postgresql/12/main /var/lib/postgresql/12/mainennebaasbakuppi
> pg_basebackup --verbose --progress --write-recovery-conf -D
> /var/lib/postgresql/12/main
> chmod --recursive --verbose 0700 /var/lib/postgresql/12/main
> chown -Rv postgres:postgres /var/lib/postgresql/12/main
> /etc/init.d/postgresql start
>

Do you get any useful output from the -v part of pg_basebackup? It should
for example tell you the exact start and stop point in the wal during the
basebackup, that can be  correlated to the msising file.

Normally the window between end of pg_basebackup and start of the actual
service is not big enough to cause a problem (since v12 will do a streaming
receive of the logs *during* the backup -- it could be a big problem before
that was possible, or if one forgot to enable it before it was the
default), and it certainly sounds weird that it should be in your case,
unless the chmod and chown commands take a *long* time. But if it is, there
is nothing preventing you from creating a slot just during setup and then
get rid of it. That is:

1. create slot
2. pg_basebackup with slot
3. start replication with slot
4. restart replication without slot  once it's caught up
5. drop slot

However, if you want reliable replication, you really should have a slot.
Or at least, you should have either a slot *or* log archiving that's
read-accessible from the replica.

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

Re: PostgreSQL 11 with SSL on Linux

[Magnus Hagander]

On Thu, Jun 4, 2020 at 5:01 PM Susan Joseph  wrote:

> Can someone tell me if there is a yum version of PostgreSQL 11 that can be
> installed on Linux that has SSL enabled?  Currently the only way I have
> gotten SSL turned on in PostgreSQL is by doing the following commands:
>
>
>- *tar xzvf /tmp/postgresql-11.2.tar.gz -C /data*
>- *mv /data/postgresql-11.2 /data/pgsql*
>- *cd /data/pgsql*
>- *./configure --prefix=/data/pgsql --without-readline --without-zlib
>--with-openssl >> conf.log*
>- *make*
>- *make install*
>
>
All the PostgreSQL versions available om yum from the postgresql.org site
have SSL enabled. Just install using the instructions from
https://www.postgresql.org/download/.

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

Re: Something else about Redo Logs disappearing

[Magnus Hagander]

s in the
pre-job and start it again in the post-job. That's by far the easiest. And
that *does* work and is fully supported.


! > The only really interesting thing there is the pg_probackup. These
> ! > folks seem to have found a way to do row-level incremental backups.
> !
> ! pg_probackup doesn't do row-level incremental backups, unless I've
> ! missed some pretty serious change in its development, but it does
> ! provide page-level,
>
> Ah, well, anyway that seems to be something significantly smaller
> than the usual 1 gig table file at once.
>

pg_probackup does page level incremental *if* you install a postgres
extension that some people have questioned the wisdom of (disclaimer: I
have not looked at this particular extension, so I cannot comment on said
wisdom). I think it also has some ability to do page level incremental by
scanning WAL. But the bottom line is it's always page level, it's never
going to be row level, based on the fundamentals of how PostgreSQL works.

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

Re: Something else about Redo Logs disappearing

[Magnus Hagander]

On Thu, Jun 11, 2020 at 10:13 PM Peter  wrote:

>
> Okay. So lets behave like professional people and figure how that
> can be achieved:
> At first, we drop that WAL requirement, because with WAL archiving
> it is already guaranteed that an unbroken chain of WAL is always
> present in the backup (except when we have a bug like the one that
> lead to this discussion).
> So this is **not part of the scope**.
>

I would assume that anybody who deals with backups professionally wouldn't
consider that out of scope, but sure, for the sake of argument, let's do
that.


! This is only one option though, there are others- you can also use
> ! pgbackrest to push your backups to s3 (or any s3-compatible data storage
> ! system, which includes some backup systems), and we'll be adding
> ! support
>
> ! I concur that this is becoming a madhouse, and is pushing past the limit
> ! for what I'm willing to deal with when trying to assist someone.
>
> Well, then that might be a misconception. I'm traditionally a
> consultant, and so I am used to *evaluate* solutions. I don't need
> assistance for that, I only need precise technical info.
>

Excellent. Then let's stick to that.


This STILL needs threaded programming (as I said, there is no way to
> avoid that with those "new API"), but in this case it is effectively
> reduced to just grab the return-code of some program that has been
> started with "&".
>

There is *absolutely* no need for threading to use the current APIs. You
need to run one query, go do something else, and then run another query.
It's 100% sequential, so there is zero need for threads. Now, if you're
stuck in shellscript, it's a little more complicated. But it does not need
threading.


But then, lets think another step forward: for what purpose do we
> actually need to call pg_start_backup() and pg_stop_backup() at all?
> I couldn't find exhaustive information about that, only some partial
> facts.
>

Since you don't trust the documentation, I suggest you take a look at
https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/access/transam/xlog.c;h=55cac186dc71fcc2f4628f9974b30850bb51eb5d;hb=92c58fd94801dd5c81ee20e26c5bb71ad64552a8#l10438

It has a fair amount of detail of the underlying reasons, and of course
links to all the details.


Things that remain to be figured out:
>  1. What does pg_start_backup actually do and why would that be
> necessary? I could not find exhaustive information, but this can
> probably figured from the source. Currently I know so much:
>  - it writes a backup_label file. That is just a few lines of
>ASCII and should not be difficult to produce.
>

It does that only in exclusive mode, and doing that is one of the big
problems with exclusive mode. So don't do that.



> I now hope very much that Magnus Hagander will tell some of the
> impeding "failure scenarios", because I am getting increasingly
> tired of pondering about probable ones, and searching the old
> list entries for them, without finding something substantial.
>

Feel free to look at the mailinglist archives. Many of them have been
explained there before. Pay particular attention to the threads around when
the deprecated APIs were actually deprecaed. I believe somebody around that
time also wrote a set of bash scripts that can be used in a
pre/post-backup-job combination with the current APIs.

//Magnus

Re: Something else about Redo Logs disappearing

[Magnus Hagander]

On Sat, Jun 13, 2020 at 10:13 PM Peter  wrote:

> On Thu, Jun 11, 2020 at 10:35:13PM +0200, Magnus Hagander wrote:
> ! > Okay. So lets behave like professional people and figure how that
> ! > can be achieved:
> ! > At first, we drop that WAL requirement, because with WAL archiving
> ! > it is already guaranteed that an unbroken chain of WAL is always
> ! > present in the backup (except when we have a bug like the one that
> ! > lead to this discussion).
> ! > So this is **not part of the scope**.
> ! >
> !
> ! I would assume that anybody who deals with backups professionally
> wouldn't
> ! consider that out of scope,
>
> I strongly disagree. I might suppose You haven't thought this to the
> proper end. See:
>

You may disagree, but I would argue that this is because you are the one
who has not thought it through. But hey, let's agree to disagree.


You can see that all the major attributes (scheduling, error-handling,
> signalling, ...) of a WAL backup are substantially different to that
> of any usual backup.

This is a different *Class* of backup object, therefore it needs an
> appropriate infrastructure that can handle these attributes correctly.
>

Yes, this is *exactly* why special-handling the WAL during the base backup
makes a lot of sense.

Is it required? No.
Will it make your backups more reliable? Yes.

But it depends on what your priorities are.


But, if You never have considered *continuous* archiving, and only
> intend to take a functional momentarily backup of a cluster, then You
> may well have never noticed these differences. I noticed them mainly
> because I did *BUILD* such an infrastructure (the 20 lines of shell
> script, you know).
>

Yes, if you take a simplistic view of your backups, then yes.


And yes, I was indeed talking about *professional* approaches.
>

Sure.



! There is *absolutely* no need for threading to use the current APIs. You
> ! need to run one query, go do something else, and then run another
> ! query.
>
> Wrong. The point is, I dont want to "go do something else", I have to
> exit() and get back to the initiator at that place.
>

That is not a requirement of the current PostgreSQL APIs. (in fact, using
threading would add a significant extra burden there, as libpq does not
allow sharing of connections between threads)

That is a requirement, and indeed a pretty sharp limitation, of the *other*
APIs you are working with, it sounds like.

The PostgreSQL APIs discussed to *not* require you to do an exit(). Nor do
they require any form of threading.

And the fact that you need to do an exit() would negate any threading
anyway, so that seems to be a false argument regardless.


This is also clearly visible in Laurenz' code: he utilizes two
> unchecked background tasks (processes, in this case) with loose
> coupling for the purpose, as it does not work otherwise.
>

Yes, because he is also trying to work around a severely limited API *on
the other side*.

There's plenty of backup integrations that don't have this limitation. They
all work perfectly fine with no need for exit() and certainly no weird need
for special threading.


The most interesting point in there appears to be this:
>   > that the backup label and tablespace map files are not written to
>   > disk. Instead, their would-be contents are returned in *labelfile
>   > and *tblspcmapfile,
>
> This is in do_pg_start_backup() - so we actually HAVE this data
> already at the *START* time of the backup!


> Then why in hell do we wait until the END of the backup before we
> hand this data to the operator: at a time when the DVD with the
>

Because it cannot be safely written *into the data directory*.

Now, it could be written *somewhere else*, that is true. And then you would
add an extra step at restore time to rename it back. But then your restore
would now also require a plugin.

(



> backup is already fixated and cannot be changed anymore, so that
>

You don't need to change the the backup, only append to it. If you are
calling pg_stop_backup() at a time when that is no longer possible, then
you are calling pg_stop_backup() at the wrong time.


As I can read, there is no difference in the function requirements
> between exclusive and non-exclusive mode, in that regard: the
> backup-label file is NOT necessary in the running cluster data tree,
> BUT it should get into the RESTORED data tree before starting it.
>

Correct. It is in fact actively harmful in the running cluster data tree.


And I can't find a single one of those "big problems". What I do find
> is just people whining that their cluster doesn't start and they can't
> simply delete a file, even if told so. Like soldier complaining that
> his gun doesn't shoot and he has no 

Re: Netapp SnapCenter

[Magnus Hagander]

On Thu, Jun 18, 2020 at 4:07 PM Paul Förster 
wrote:

> Hi Ken,
>
> > On 18. Jun, 2020, at 15:56, Wolff, Ken L  wrote:
> > PostgreSQL doesn’t need to be in a special mode for backups to work.
>
> this is curious. Doesn't the PostgreSQL cluster need to be set to backup
> mode to use SnapCenter?
>

I don't know specifically about SnapCenter, but for snapshots in general,
it does require backup mode *unless* all your data is on the same disk and
you have an atomic snapshot across that disk (in theory it can be on
different disk as well, as long as the snapshots in that case are atomic
across *all* those disks, not just individually, but that is unusual).

So the upthread suggestion of putting data and wal on different disk and
snapshoting them at different times is *NOT* safe. Unless the reference to
the directory for the logs means a directory where log files are copied out
with archive_command, and it's actually the log archive (in which case it
will work, but the recommendation is that the log archive should not be on
the same machine).


The problem is, one can't test that and get a reliable answer, because you
> do 100 backups (snaps) for testing and restore those 100 backups, and all
> may be ok.
>

> But what about the 1000th snap? Just in that millisecond of the snap, some
> important information is written to the volume on which the PostgreSQL
> cluster resides and for some reason, it needs to be restored later and this
> information is lost or may lead to corruption. This is why backup mode
> exists, after all. Really, no backup mode by a pre/post script when
> snapping?
>

The normal case is that snapshots are guaranteed to be atomic, and thus
this millisecond window cannot appear. But that usually only applies across
individual volumes. It's also worth noticing that taking the backups
without using the backup mode and archive_command means you cannot use them
for PITR, only for restore onto that specific snapshot.

While our documentation on backups in general definitely needs improvement,
this particular requirement is documented at
https://www.postgresql.org/docs/current/backup-file.html.

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

Re: Netapp SnapCenter

[Magnus Hagander]

On Sat, Jun 20, 2020 at 9:04 AM Paul Förster 
wrote:

> Hi Stephen,
>
> > On 19. Jun, 2020, at 18:02, Stephen Frost  wrote:
> > When it comes to 'backup mode', it's actually the case that there can be
> > multiple backups running concurrently because there isn't actually a
> > single 'cluster wide backup mode', really.
>
> this is what I don't understand. Why would there be a reason to run
> multiple backups concurrently? I mean, using pg_start_backup() means I then
> have to backup (using whatever method) of the while PGDATA and not just
> some random file ${PGDATA}/base/13297/2685. And since I have to backup the
> whole of PGDATA anyway, why would I want to do that multiple times, even
> more so, concurrently?
>
> I read the backup doc but I just can't grasp the idea of why multiple
> concurrent backup capabilities should yield any benefit at all.
>

One not uncommon case is for example being able to provision a new replica
while a backup is running. Since replicas are provisioned starting off a
base backup, being able to run that concurrently is very useful. Especially
if base backups take a long time to run.

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

Re: Netapp SnapCenter

[Magnus Hagander]

On Mon, Jun 22, 2020 at 8:02 AM Paul Förster 
wrote:

> Hi Stephen,
>
> > On 22. Jun, 2020, at 07:36, Stephen Frost  wrote:
> > That's not the only case that I, at least, have heard of- folks aren't
> > really very happy with their backups fail when they could have just as
> > well completed, even if they're overlapping.  Sure, it's better if
> > backups are scheduled such that they don't overlap, but that can be hard
> > to guarantee.
>
> I see.
>


Yeah, especially when your backups are a number of TB which makes them take
Some Time (TM) to complete...



> The thing about this is though that the new API avoids *other* issues,
> > like what happens if the system crashes during a backup (which is an
> > entirely common thing that happens, considering how long many backups
> > take...) and it does so in a relatively reasonable way while also
> > allowing concurrent backups, which is perhaps a relatively modest
> > benefit but isn't the main point of the different API.
>
> that makes me curious about another thing. The output of pg_stop_backup()
> is to be stored. Otherwise the backup is useless. So far, so good. But what
> if the server crashes in the middle of the backup and pg_stop_back() hence
> is never reached? In this case, it obviously does not create any output.
>

Whenever the connection that ran pg_start_backup() disconnects without
calling pg_stop_backup(), the "state" of being "in backup mode" is "rolled
back" in the database. So similar to how a transaction you started with
BEGIN gets rolled back if you just disconnect without issuing COMMIT.

Your backup will of course be invalid in this case, but the database itself
will be fine. (And the inability to ensure this is exactly why the old
"exclusive mode" for backups is deprecated -- but the non-exclusive mode is
safe with this) So it is of course very important to check that the
pg_stop_backup() step completed successfully, and fail the entire backup if
it did not.


Ok, you usually start the server, the database does a crash recovery and
> opens. Then, some time later, you do the usual backup and all is well. This
> is like 99.999% of all cases.
>

> But what if you need to restore to the latest transaction while the
> database was running in backup mode during which the crash occurred. How
> does that work if no pg_stop_backup() output exists? Did I miss something
> here?
>

It does not work off *that* base backup. But if you start from the *prior*
be backup (one that did complete with a successful pg_stop_backup) then you
can still use the archived wal to recover to any point in time.

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

Re: autovacuum failing on pg_largeobject and disk usage of the pg_largeobject growing unchecked

[Magnus Hagander]

On Mon, Jun 22, 2020 at 10:01 PM Jim Hurne  wrote:

> We're still struggling to figure out why autovacuum seems to be failing or
> is ineffective sometimes against the pg_largeobject table.
>
> We decided to try a VACUUM FULL VERBOSE on one of our problematic
> databases. It did complete, but it took a surprisingly long time:
>
>   INFO:  vacuuming "pg_catalog.pg_largeobject"
>   INFO:  "pg_largeobject": found 97326130 removable, 22230 nonremovable
> row versions in 207508868 pages
>   DETAIL:  0 dead row versions cannot be removed yet.
>   CPU: user: 1085.87 s, system: 3803.66 s, elapsed: 10883.94 s.
>   VACUUM
>
> So it took about 3 hours.  It's surprising because there was only 58 MB of
> undeleted data in about 65 large objects. When we ran this particular
> vacuum, we made sure there were no other processes connected to the DB and
> that no other statements were running that would have locked the table.
> It's my understanding that a VACUUM FULL works by creating a brand new
> table and copying the "non-deleted" data into the new table. If that's the
> case, I would have expected it to complete very quickly, given how little
> "non-deleted" data existed at the time.
>

Yes, that's how VACUUM FULL works (more or less).

It had to read 207508868 pages, which is about 1.5TB worth of data
(207508868 pages of 8k), in order to know it didn't need it. That's
probably what took time. Depending on the performance of the machine, it
does not seem unreasonable. (The problem, of course, being that it got to
this big size with that little actual useful data in the table)

And for autovacuum, with a cost_delay of 20ms and a cost_limit of 200,
autovacuum would spend about 55 hours just on vacuum delay for the reads
(assuming all are cache failures and thus cost 10 "units", but that's
probably close enough to give you an idea) if my math isn't off
(reading 207508868 pages would then trigger the cost limit 207508868/20
times, and sleep 20ms each of those times).


We are of course going to continue to try different things, but does
> anyone have any other suggestions on what we should be looking at or what
> settings we might want to adjust?
>

The logs you posted originally seem to be excluding the actual autovacuum
details -- can you include those? That is, you are only including the very
last row of the log message, but the interesting parts are at the beginning.

I assume you've also looked for other autovacuum messages in the log --
such as it being canceled by concurrent activity?

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

Re: Error in Table Creation

[Magnus Hagander]

On Thu, Jun 25, 2020 at 10:59 AM Rajnish Vishwakarma <
rajnish.nationfi...@gmail.com> wrote:

> I am creating dynamically table in PostgreSQL using psycopg2 by passing
> the below 2 strings as column names:
>
>
> 'BAF7_X_X_During_soaking-__Temperature__difference_coil_to_coil_with_metal_temp_TC_load_in_PA_load'
>
> and
>
>
> 'BAF7_X_X_During_soaking-__Temperature__difference_coil_to_coil_with_metal_temp_TC_load__in_TA_load'
>
> And the above column names are not same ( both are different columns ) and
> both the columns has string same till   
> *'BAF7_X_X_During_soaking-__Temperature__difference_coil_to_coil_with_metal_temp_TC_load__
> .*
>
> but i am getting errors as
>
> Error:
> Traceback (most recent call last):
>   File 
> "C:/Users/Administrator/PycharmProjects/untitled/table_creation_with_HDA_Data.py",
>  line 131, in 
> cursor.execute(sqlCreateTable)
> psycopg2.errors.DuplicateColumn: column 
> "BAF7_X_X_During_soaking-__Temperature__difference_coil_to_coil_" specified 
> more than once
>
> The above columns are of type TEXT ...also it may be Numeric Type in
> future.
>
> Require assistance from Postgres team on the above error.
>
>
By default the maximum length of a column name (or other names) in postgres
is 63 characters, see
https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS).
You are trying to create columns with names that are way longer than that,
so they will be truncated. If you create this table in psql it will show
you a NOTICE information about this -- I believe in psycopg2 this shows up
in conn.noticies (where conn is the connection object from psycopg2).

And truncated to 63 characters they *are* the same name. (Those are some
crazy long column names btw..)

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

Re: EXTERNAL: Re: Netapp SnapCenter

[Magnus Hagander]

On Thu, Jun 25, 2020 at 5:23 PM Paul Förster 
wrote:

> Hi Ken,
>
> > On 25. Jun, 2020, at 17:15, Wolff, Ken L  wrote:
> > There's actually a lot of good stuff in that document about Postgres in
> general.  I'd be curious to hear what everyone thinks, though, and
> specifically about what NetApp recommends in Section 3.3 about putting data
> and WAL on separate volumes, which I believe contradicts what's been
> discussed in this email thread.
>
> yes, I've read the part about different volumes and I must say, I don't
> agree because I think it violates atomicity.
>

I believe NetApp does atomic snapshots across multiple volumes, if you have
them in the same consistency group. (If you don't then you're definitely in
for a world of pain if you ever have to restore)

Snapshotting multiple volumes in a consistency group will set up a write
fence across them, then snapshot, and AIUI guarantees correct write
ordering.

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

Re: PostgreSQL database segsize

[Magnus Hagander]

On Tue, Jun 30, 2020 at 12:17 AM Bill Glennon  wrote:

> Hi,
>
> If you are building a Postgresql database from source and you use
> option --with-segsize=4, how do you verify that the database segsize is 4GB
> and not the default 1GB? Is there a query that you can run?
>
> Or even if you come into a place to support an existing PostgreSQL
> database, how do you find out what the database segsize is?
>
>
You can run the query "SHOW segment_size" to show the compiled-in value.

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

Re: Different results from identical matviews

[Magnus Hagander]

On Thu, Jul 2, 2020 at 2:02 AM Tom Lane  wrote:

> Anders Steinlein  writes:
> > We have a materialized view from which a customer reported some
> > confusing/invalid results, leading us to inspect the query and not
> finding
> > anything wrong. Running the query defining the matview manually, or
> > creating a new (identical) materialized view returns the correct result.
> > Obviously, we've done REFRESH MATERIALIZED VIEW just before doing the
> > comparison, and all runs are in the same schema.
>
> I suspect the query underlying the matviews is less deterministic than
> you think it is.  I did not study that query in any detail, but just
> from a quick eyeball: the array_agg() calls with no attempt to enforce a
> particular aggregation order are concerning, and so is grouping by
> a citext column (where you'll get some case-folding of a common value,
> but who knows which).
>

Also not having looked at the query in detail -- but are there concurrent
changes in the database? Because since you're creating your transaction in
READ COMMITTED, other transactions finishing in between your two REFRESH
commands can alter the data. To make sure that's not what's happening, you
may want to try doing the same thing with a BEGIN  TRANSACTION ISOLATION
LEVEL SERIALIZABLE instead, and see if the problem still occurs.

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

Re: Security Vulnerability on PostgreSQL VMs

[Magnus Hagander]

On Fri, Jul 17, 2020 at 5:44 PM Hilbert, Karin  wrote:

> We have PostgreSQL v9.6 & also PostgreSQL v11.8 installed on various Linux
> VMs with Red Hat Enterprise Linux Server release 7.8 (Maipo) OS.  We're
> also running repmgr v5.1.0 & PgBouncer v1.13.
>
> We're getting vulnerability reports from our Security Office for the
> following packages:
>  - python-pulp-agent-lib-2.13.4.16-1.el7sat
>  - python-gofer-2.12.5-5.el7sat
>
> For some reason these packages aren't being updated to the current
> versions & our Linux Admins haven't been able to resolve the update
> issue.  It has something to do with a satellite?   (I'm not a Linux Admin -
> I don't really know what they're talking about).  Anyway, *are these
> packages anything that would be required by PostgreSQL, repmgr or
> PgBouncer?*  It's nothing that I installed on the VMs - I assume that
> it's something installed along with the OS.  The Linux Admin's
> recommendation is to just remove these packages.
>

They are not. They are part Pulp for example, but in particular they are
part of RedHat Satellite which is probably why the package version has a
name ending in "sat". So it would be something a Linux admin would put in
there, not the DBA.

But to answer the question, no they are not required by PostgreSQL, repmgr
or pgbouncer.

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

Re: > ERROR: syntax error at or near "BYTE"

[Magnus Hagander]

On Fri, Aug 21, 2020 at 10:33 AM postgresdba...@outlook.com <
postgresdba...@outlook.com> wrote:

> CREATE TABLE "A"
> (
>   "b" DATE,
>   "c  "   NUMBER,
>  " d "  VARCHAR2(255 BYTE),
>   "e "VARCHAR2(255 BYTE))
>
>   When ı create table then after error why error in byte please heplp me
> thanks
>
>   error:> ERROR:  syntax error at or near "BYTE"
>

This is not valid syntax in PostgreSQL (or I believe, in SQL in general).
This is Oracle syntax.

PostgreSQL does not have the number data type, so you'll eventually get a
problem there as well. Which data type to use instead depends on what data
you are actually going to store.

PostgreSQL does not have the varchar2 data type (so you will have to use
varchar).

And finally, varchar just takes a number, not the special construct with
BYTE. PostgreSQL varchar:s always limit the size based on number of
characters, not bytes.

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

Re: Fix typo in Comments of HandleProcSignalBarrierInterrupt

[Magnus Hagander]

On Thu, Sep 3, 2020 at 12:43 PM Hou, Zhijie 
wrote:

> Hi all
>
> In master branch, I found a typo in Comments of function
> HandleProcSignalBarrierInterrupt.
> See the attachment for the patch.
>
>
Thanks, pushed.

//Magnus

Re: Autovacuum of independent tables

[Magnus Hagander]

(Please don't drop the mailinglist from CC, as others are likely interested
in the responses)

On Tue, Sep 8, 2020 at 3:06 PM Michael Holzman 
wrote:

>
>
> On Tue, Sep 8, 2020 at 3:03 PM Magnus Hagander wrote:
>
>> A PostgreSQL SELECT does *not* open a transaction past the end of the
>> statement, if it's run independently on a connection.
>>
> This sounds like you are using a client on PostgreSQL that uses an
>> "autocommit off" mode, since that's the only case where you'd need to add
>> COMMITs (or ROLLBACKs) to close a transaction after a SELECT.
>>
>
> Yes, this is correct. We do not use autocommit. Everything is controlled
> explicitly. We run quite complex multi-statement multi-table transactions
> and cannot work with "autocommit on".
>

That is not what autocommit means.

Whether you have autocommit on or off, you can *always* control things
explicitly. And you can certainly run "multi-statement transactions" in
autocommit on -- in fact, it's what most people do since it's the default
configuration of the system (and I don't see why multi-table would even be
relevant).

Autocommit on/off only controls what happens when you *don't* control
things explicitly.



> Therefore, this is what we have
>
> > psql
> psql (11.2)
> Type "help" for help.
>
> pg-11.2 rw => COMMIT;
> WARNING:  25P01: there is no transaction in progress
> LOCATION:  EndTransactionBlock, xact.c:3675
> COMMIT
> Time: 0.745 ms
> pg-11.2 rw => select 2*2;
>  ?column?
> --
> 4
> (1 row)
>
> Time: 0.347 ms
> pg-11.2 rw => COMMIT;
> COMMIT
> Time: 0.525 ms
>
> The first COMMIT (immediately after connect) fails as there is no
> transaction.
> The second one works as even this SELECT opened one. We have a transaction
> (and a snapshot) when no table is touched!
>

So just to be clear,  here is how PostgreSQL behaves by default:

postgres=# commit;
WARNING:  there is no transaction in progress
COMMIT
postgres=# select 2*2;
 ?column?
--
4
(1 row)

postgres=# commit;
WARNING:  there is no transaction in progress
COMMIT



But yes, if you explicitly ask that a query shall keep a transaction open
across multiple statements, by turning off autocommit, it will.

In fact, *PostgreSQL* will always behave that way. The *psql client* will
behave differently depending on how you configure it, and the same will of
course apply to any other client that you have. In the example above, psql.


You cannot both have a transaction existing and not existing at the same
time. You do have to separate the idea of transactions from snapshots
though, as they can differ quite a bit depending on isolation levels.


> And how much a running transaction blocks autovacuum is also dependent on
> what isolation level you're running it in. In the default isolation level,
> a snapshot is taken for each individual select, so does not block vacuuming
> past the end of the individual select. Higher isolation levels will.
>
> > We use default isolation mode and we proved that SELECTs block
autovacuum. As soon as we added COMMITs after SELECTS in several places
(not all as we still have not fixed all the code), autovacuum started
working properly in the fixed flows.


As I said yes, a running SELECT will, because of the snapshot. An open
transaction will not, past the individual select, because a new snapshot is
taken for each SELECT.

If you have an open transaction that runs regular selects but as separate
queries then it will not block autovacuum, unless it also does something
else.

Of course if it's a big query that runs the whole time it will, but then
there would also not be a way to "add commits" into the middle of it, so
clearly that's not what's going on here.

//Magnus

Re: Autovacuum of independent tables

[Magnus Hagander]

On Tue, Sep 8, 2020 at 4:01 PM Michael Holzman 
wrote:

>
>
> On Tue, Sep 8, 2020 at 4:25 PM Magnus Hagander wrote:
>
>>
>>
>> Whether you have autocommit on or off, you can *always* control things
>> explicitly. And you can certainly run "multi-statement transactions" in
>> autocommit on -- in fact, it's what most people do since it's the default
>> configuration of the system (and I don't see why multi-table would even be
>> relevant).
>>
>> Autocommit on/off only controls what happens when you *don't* control
>> things explicitly.
>>
> I know that we can control things explicitly with "autocommit on". But we
> would need to add "BEGIN" statements to the code which is an even bigger
> change than adding COMMITs. We considered it and found that the development
> cost is too high.
>
> It seems I was not clear enough. I do not complain. I have been a PG fan
> since 2000 when I worked with it for the first time. I just wanted to
> understand it deeper and, fortunately, find a work around that would
> simplify our current development.
>
>
Oh sure, but there is clearly *something* going on, so we should try to
figure that out. Because a transaction running multiple independent selects
with the defaults settings will not actually block autovacuum. So clearly
there is something else going on -- something else must be non-default, or
it's something that the driver layer does.

To show that, something as simple as the following, with autovacuum logging
enabled:

session 1:
CREATE TABLE test AS SELECT * FROM generate_series(1,1);

session 2:
begin;
SELECT count(*) FROM test;
\watch 1

session 1:
delete from test;


In this case, you will see autovacuum firing just fine, even though there
is an open transaction that queries the table test. As you're running you
can use a third session to see that session 2 flips between "active" and
"idle in transaction". The log output in my case was:

2020-09-08 16:13:12.271 CEST [26753] LOG:  automatic vacuum of table
"postgres.public.test": index scans: 0
pages: 0 removed, 45 remain, 0 skipped due to pins, 0 skipped frozen
tuples: 56 removed, 0 remain, 0 are dead but not yet removable, oldest
xmin: 241585
buffer usage: 112 hits, 4 misses, 5 dirtied
avg read rate: 0.006 MB/s, avg write rate: 0.008 MB/s
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 5.01 s

It is failing to *truncate* the table, but the general autovacuum is
running.

Are you by any chance specifically referring to the truncation step?

However, if you change the session 2 to select from a *different* table,
the truncation also works, so I'm guessing that's not it?

//Magnus

Re: Autovacuum of independent tables

[Magnus Hagander]

On Tue, Sep 8, 2020 at 4:38 PM Tom Lane  wrote:

> Magnus Hagander  writes:
> > Oh sure, but there is clearly *something* going on, so we should try to
> > figure that out. Because a transaction running multiple independent
> selects
> > with the defaults settings will not actually block autovacuum.
>
> I don't think the OP is claiming that autovacuum is blocked, only that
> it's failing to remove recently-dead rows that he thinks could be removed.
>
> The reason that's not so is that whether or not transaction A *has*
> touched table B is irrelevant.  It *could* read table B at any moment,
> for all autovacuum knows.  Therefore we cannot remove rows that should
> still be visible to A's snapshot.
>
> There are some approximations involved in figuring out which rows are
> potentially still visible to someone.  So perhaps this is a situation
> where an approximation is being used and tighter analysis would have
> shown that indeed a row could be removed.  But we haven't seen any
> evidence of that so far.  The basic fact that A's snapshot is limiting
> removal of rows from a table it has not touched is not a bug.
>

Right. But in the default isolation level, the snapshot of A gets reset
between each SELECT, and does not persist to the end of the transaction. So
adding COMMIT between each select shouldn't change that part, should it?
That is, it's the snapshot age that decides it, not the transaction age.

I feel there is still some piece of information missing there, that could
explain the problem better...

//Magnus

Re: Autovacuum of independent tables

[Magnus Hagander]

On Tue, Sep 8, 2020 at 5:16 PM Tom Lane  wrote:

> Magnus Hagander  writes:
> > On Tue, Sep 8, 2020 at 4:38 PM Tom Lane  wrote:
> >> The reason that's not so is that whether or not transaction A *has*
> >> touched table B is irrelevant.  It *could* read table B at any moment,
> >> for all autovacuum knows.  Therefore we cannot remove rows that should
> >> still be visible to A's snapshot.
>
> > Right. But in the default isolation level, the snapshot of A gets reset
> > between each SELECT, and does not persist to the end of the transaction.
>
> Well, we don't know what isolation level the OP is using.  We also don't
>

Per the thread, he's using the default, which should be read committed.



> know what PG version he's using.  From memory, it hasn't been that long
>

Per his session list, 11.2.



> since we fixed things so that an idle read-committed transaction
> advertises no xmin.  It's also possible that the transaction isn't really
> idle between statements (eg, if it's holding open cursors, or the like).
>

Oh, now *cursors* is definitely something I didn't think of. And especially
in the context of ODBC, I wonder if it might be creating cursors
transparently, and that this somehow causes the problems.

Michael, do you know if that might be the case? Or try enabling
log_statements to check if it is?

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

Re: Reg:CHARSET_COVERSION_LATIN_TO_UTF8

[Magnus Hagander]

On Mon, Sep 14, 2020 at 7:17 AM nandha kumar 
wrote:

> Hi Team,
> I have the postgresql database with 11.4 Version in AWS RDS. Some of the
> columns have lattin collate format. I need to migrate the database to Azure
> postgresql and need to convert UTF8 column format.
>
> How to find which columns are in Latin format. How to convert the data and
> columns to UTF8.
>
>
PostgreSQL doesn't have different encoding (aka charset) on different
columns, only on databases. Latin1 and UTF8 are encodings. Columns can have
different collations, but not different encodings.

You can check your database encodings with \l in psql. All tables and
columns in one database will have the same encoding as the one listed for
the database. If you are using pg_dump to do the migration, you can use the
-E parameter to make sure the data is dumped in UTF8 format, reload that,
and all should be taken care of.

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

Re: multiple tables got corrupted

[Magnus Hagander]

On Tue, Sep 15, 2020 at 11:15 AM Vasu Madhineni 
wrote:

> Hi All,
>
> In one of my postgres databases multiple tables got corrupted and followed
> the below steps but still the same error.
>
> 1.SET zero_damaged_pages = on
> 2. VACUUM ANALYZE, VACUUM FULL
> but still same error.
>


That is a very destructive first attempt. I hope you took a full disk-level
backup of the database before you did that, as it can ruin your chances for
forensics and data recovery for other issues.


moh_fa=# VACUUM FULL;
> ERROR:  could not read block 9350 in file "base/1156523/1270812":
> Input/output error
>
> Tried to take backup of tables with pg_dump but same error. files exist
> physically in base location.
>
> How to proceed on this, no backup to restore.
>
>
This is clearly some sort of disk error, and with no backups to restore you
will definitely be losing data.

I'd start by figuring out which tables have no corruption and do work, and
back those up (with pg_dump for example) as soon as possible to a different
machine -- since it's not exactly unlikely that further disk errors will
appear.

Once you've done that, identify the tables, and then try to do partial
recovery. For example, if you look at the file 1270812, how big it is?
PostgreSQL is failing to read block 9350 which is 76595200 bytes into the
file. If this is at the very end of the file, you can for example try to
get the data out until that point with LIMIT. If it's in the middle of the
file, it gets more ticky, but similar approaches can be done.

Also, unless you are running with data checksums enabled, I wouldn't fully
trust the data in the tables that you *can* read either. Since you clearly
have disk issues, they may have caused corruption elsewhere as well, so
whatever verification you can do against other tables, you should do as
well.


You'll of course also want to check any kernel logs or storage system logs
to see if they can give you a hint as to what happened, but they are
unlikely to actually give you something that will help you fix the problem.

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

Re: multiple tables got corrupted

[Magnus Hagander]

Try reading them "row by row" until it breaks. That is, SELECT * FROM ...
LIMIT 1, then LIMIT 2 etc. For more efficiency use a binary search starting
at what seems like a reasonable place looking at the size of the table vs
the first failed block to make it faster, but the principle is the same.
Once it fails, you've found a corrupt block...

//Magnus


On Tue, Sep 15, 2020 at 12:46 PM Vasu Madhineni 
wrote:

> Is it possible to identify which rows are corrupted in particular tables.
>
> On Tue, Sep 15, 2020 at 5:36 PM Magnus Hagander 
> wrote:
>
>>
>>
>> On Tue, Sep 15, 2020 at 11:15 AM Vasu Madhineni 
>> wrote:
>>
>>> Hi All,
>>>
>>> In one of my postgres databases multiple tables got corrupted and
>>> followed the below steps but still the same error.
>>>
>>> 1.SET zero_damaged_pages = on
>>> 2. VACUUM ANALYZE, VACUUM FULL
>>> but still same error.
>>>
>>
>>
>> That is a very destructive first attempt. I hope you took a full
>> disk-level backup of the database before you did that, as it can ruin your
>> chances for forensics and data recovery for other issues.
>>
>>
>> moh_fa=# VACUUM FULL;
>>> ERROR:  could not read block 9350 in file "base/1156523/1270812":
>>> Input/output error
>>>
>>> Tried to take backup of tables with pg_dump but same error. files exist
>>> physically in base location.
>>>
>>> How to proceed on this, no backup to restore.
>>>
>>>
>> This is clearly some sort of disk error, and with no backups to restore
>> you will definitely be losing data.
>>
>> I'd start by figuring out which tables have no corruption and do work,
>> and back those up (with pg_dump for example) as soon as possible to a
>> different machine -- since it's not exactly unlikely that further disk
>> errors will appear.
>>
>> Once you've done that, identify the tables, and then try to do partial
>> recovery. For example, if you look at the file 1270812, how big it is?
>> PostgreSQL is failing to read block 9350 which is 76595200 bytes into the
>> file. If this is at the very end of the file, you can for example try to
>> get the data out until that point with LIMIT. If it's in the middle of the
>> file, it gets more ticky, but similar approaches can be done.
>>
>> Also, unless you are running with data checksums enabled, I wouldn't
>> fully trust the data in the tables that you *can* read either. Since you
>> clearly have disk issues, they may have caused corruption elsewhere as
>> well, so whatever verification you can do against other tables, you should
>> do as well.
>>
>>
>> You'll of course also want to check any kernel logs or storage system
>> logs to see if they can give you a hint as to what happened, but they are
>> unlikely to actually give you something that will help you fix the problem.
>>
>>

Re: Obvious data mismatch in View2 which basically SELECT * from View1

[Magnus Hagander]

On Wed, Sep 16, 2020 at 9:26 AM Ben  wrote:

> Dear list,
>
> Recently I am getting feedback, data in my analytic report is not
> repeatable. From time to time they get different data for the same time
> span.
> (but IIRC previously it was OK). Therefore I started debuging the View
> chain for that report, during which I bumped into this issue/phenomenon.
>
> In a over -simplified version:
>
> CREATE VIEW2 AS SELECT * FROM VIEW1;
> SELECT  col1 FROM VIEW2 WHERE cond1=True;
> SELECT  col1 FROM VIEW1 WHERE cond1=True;
>
> Now col1 from both views looks different. I don't know where to start to
> solve this problem.
>
> The actual situation is a bit more than that, the following is the
> actual query:
>
>
>  -- trying to audit utlog weighed stat
>  with t as (
>  select '2020-07-01 00:00:00'::timestamp t0, '2020--07-02
> 0:0:0'::timestamp t1
>  )
>  --select * from t;
>  select *
>  -- from utlog.cache_stats_per_shift_per_reason_weighed_stats
>  -- from utlog.stats_per_shift_filtered_per_reason
>  from utlog.stats_per_shift_filtered (let's call
> it #View2 for short)
>  -- from utlog.stats_per_shift_filtered_b0206  (let's call it
> #View1 for short)
>  -- from utlog.stats_per_shift
>  cross join t
>  where wline = 'F02'  and wts >= t.t0 and wts < t.t1 and wsft ='D'
>  limit 100
>  ;
>


Not sure if it might be something lost in your simplification here, but you
have a LIMIT with no ORDER BY there. That basically means "give me 100
random rows" (but not with a very good random level). It does not return
rows in a consistent/predictable order. So as long as that query is part of
what you're doing, you should not be surprised if you get the rows in an
inconsistent/unpredictable order, with whatever follow-on effects that
might have. (And it can lead to weird follow-on effects like the ones
you're talking about when used in larger query structures)

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

Re: multiple tables got corrupted

[Magnus Hagander]

That depends on what the problem is and how they fix it. Most likely yes --
especially since if you haven't enabled data checksums you won't *know* if
things are OK or not. So I'd definitely recommend it even if things *look*
OK.

//Magnus


On Wed, Sep 16, 2020 at 5:06 AM Vasu Madhineni 
wrote:

> I could see block read I/O errors in /var/log/syslog. if those error fixed
> by OS team, will it require recovery.
>
> Also can i use LIMIT and OFFSET to locate corrupted rows?
>
> Thanks in advance
>
> Regards,
> Vasu Madhineni
>
> On Wed, Sep 16, 2020, 01:58 Magnus Hagander  wrote:
>
>> Try reading them "row by row" until it breaks. That is, SELECT * FROM ...
>> LIMIT 1, then LIMIT 2 etc. For more efficiency use a binary search starting
>> at what seems like a reasonable place looking at the size of the table vs
>> the first failed block to make it faster, but the principle is the same.
>> Once it fails, you've found a corrupt block...
>>
>> //Magnus
>>
>>
>> On Tue, Sep 15, 2020 at 12:46 PM Vasu Madhineni 
>> wrote:
>>
>>> Is it possible to identify which rows are corrupted in particular tables.
>>>
>>> On Tue, Sep 15, 2020 at 5:36 PM Magnus Hagander 
>>> wrote:
>>>
>>>>
>>>>
>>>> On Tue, Sep 15, 2020 at 11:15 AM Vasu Madhineni 
>>>> wrote:
>>>>
>>>>> Hi All,
>>>>>
>>>>> In one of my postgres databases multiple tables got corrupted and
>>>>> followed the below steps but still the same error.
>>>>>
>>>>> 1.SET zero_damaged_pages = on
>>>>> 2. VACUUM ANALYZE, VACUUM FULL
>>>>> but still same error.
>>>>>
>>>>
>>>>
>>>> That is a very destructive first attempt. I hope you took a full
>>>> disk-level backup of the database before you did that, as it can ruin your
>>>> chances for forensics and data recovery for other issues.
>>>>
>>>>
>>>> moh_fa=# VACUUM FULL;
>>>>> ERROR:  could not read block 9350 in file "base/1156523/1270812":
>>>>> Input/output error
>>>>>
>>>>> Tried to take backup of tables with pg_dump but same error. files
>>>>> exist physically in base location.
>>>>>
>>>>> How to proceed on this, no backup to restore.
>>>>>
>>>>>
>>>> This is clearly some sort of disk error, and with no backups to restore
>>>> you will definitely be losing data.
>>>>
>>>> I'd start by figuring out which tables have no corruption and do work,
>>>> and back those up (with pg_dump for example) as soon as possible to a
>>>> different machine -- since it's not exactly unlikely that further disk
>>>> errors will appear.
>>>>
>>>> Once you've done that, identify the tables, and then try to do partial
>>>> recovery. For example, if you look at the file 1270812, how big it is?
>>>> PostgreSQL is failing to read block 9350 which is 76595200 bytes into the
>>>> file. If this is at the very end of the file, you can for example try to
>>>> get the data out until that point with LIMIT. If it's in the middle of the
>>>> file, it gets more ticky, but similar approaches can be done.
>>>>
>>>> Also, unless you are running with data checksums enabled, I wouldn't
>>>> fully trust the data in the tables that you *can* read either. Since you
>>>> clearly have disk issues, they may have caused corruption elsewhere as
>>>> well, so whatever verification you can do against other tables, you should
>>>> do as well.
>>>>
>>>>
>>>> You'll of course also want to check any kernel logs or storage system
>>>> logs to see if they can give you a hint as to what happened, but they are
>>>> unlikely to actually give you something that will help you fix the problem.
>>>>
>>>>

Re: What version specification used by PG community developers?

[Magnus Hagander]

On Wed, Oct 7, 2020 at 3:47 PM Adrian Klaver 
wrote:

> On 10/7/20 6:01 AM, Ron wrote:
> > On 10/7/20 2:52 AM, WanCheng wrote:
> >> Is same to the SemVer?(https://semver.org/)
> >
> > It used to be, but starting with v10 it's
> > MAJOR
> > PATCH
>
> Was it?
>
> Pre-10 it was:
>
> MAJOR.MAJOR.PATCH
>


Yeah the fact that it kind of looked like semver, but *wasn't* semver, is
probably one of the (many) things that confused people. It definitely
wasn't semver.

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

Re: Parameter value from (mb/gb) to bytes

[Magnus Hagander]

On Wed, Oct 14, 2020 at 3:57 PM Thomas Kellerer  wrote:

> Thomas Kellerer schrieb am 14.10.2020 um 15:55:
> > Raul Kaubi schrieb am 14.10.2020 um 12:22:
> >> Is there a simple way to dynamically get for example parameter
> >> „shared buffers“ value (megabytes or gigabytes) to bytes, for
> >> monitoring perspective..?>
> >>
> >>
> >> At the moment, this gives me value in GB.
> >>
> >> # psql -U postgres -Atc "show shared_buffers;"
> >> 1GB
> >>
> >> This value may as well be in MB. So I am looking a way to dynamically
> get the value in bytes.
> >
> > Instead of using "show" you can use a SELECT with pg_size_bytes():
> >
> >   select pg_size_bytes(setting)
> >   from pg_settings
> >   where name = 'shared_buffers';
>
> Ah, forgot that shared_buffers is in 8K pages.
>
> So you actually need:
>
>select pg_size_bytes(setting) * 8192
>from pg_settings
>where name = 'shared_buffers';
>

Actually, it doesn't have to be in 8k pages, that depends on the build
options. So if you want to be perfectly correct, you should probably
multiply with current_setting('block_size') instead of a hardcoded 8192 :)

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

Re: Parameter value from (mb/gb) to bytes

[Magnus Hagander]

On Wed, Oct 14, 2020 at 5:10 PM Tom Lane  wrote:

> Magnus Hagander  writes:
> > On Wed, Oct 14, 2020 at 3:57 PM Thomas Kellerer  wrote:
> >> select pg_size_bytes(setting) * 8192
> >> from pg_settings
> >> where name = 'shared_buffers';
>
> > Actually, it doesn't have to be in 8k pages, that depends on the build
> > options. So if you want to be perfectly correct, you should probably
> > multiply with current_setting('block_size') instead of a hardcoded 8192
> :)
>
> It's fairly annoying that this doesn't work:
>
> regression=# select pg_size_bytes(setting||' '||unit) from pg_settings
> where name = 'shared_buffers';
> ERROR:  invalid size: "16384 8kB"
> DETAIL:  Invalid size unit: "8kB".
> HINT:  Valid units are "bytes", "kB", "MB", "GB", and "TB".
>
> Maybe we should teach pg_size_bytes to cope with that.
>

Actually thinking though, surely *this* particular case can be spelled as:
SELECT  pg_size_bytes(current_setting('shared_buffers'))

Or if doing it off pg_settings:

SELECT setting::bigint * pg_size_bytes(unit) from pg_settings where
name='shared_buffers'

I'm not sure having pg_size_bytes() parse "16384 8kB" is reasonable, I have
a feeling that could lead to a lot of accidental entries giving the wrong
results.

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

Re: Parameter value from (mb/gb) to bytes

[Magnus Hagander]

On Wed, Oct 14, 2020 at 5:23 PM Tom Lane  wrote:

> Magnus Hagander  writes:
> > On Wed, Oct 14, 2020 at 5:10 PM Tom Lane  wrote:
> >> It's fairly annoying that this doesn't work:
> >> regression=# select pg_size_bytes(setting||' '||unit) from pg_settings
> >> where name = 'shared_buffers';
>
> > Actually thinking though, surely *this* particular case can be spelled
> as:
> > SELECT  pg_size_bytes(current_setting('shared_buffers'))
>
> Yeah, that might be the most recommendable way.
>
> > Or if doing it off pg_settings:
> > SELECT setting::bigint * pg_size_bytes(unit) from pg_settings where
> > name='shared_buffers'
>
> No, because that will fail for any unit other than '8kB', eg
>
> regression=# select pg_size_bytes('MB');
> ERROR:  invalid size: "MB"
>

Right, but it would certainly work for *this* case using pg_asettings, is
what I meant.

That said, I think it'd then actually be better to teach pg_size_bytes to
know that "MB" is the same as "1MB" and parse that. That might be something
that would actually be useful in other cases as well -- basically as a way
to get conversion units in general. Basically if the string is "unit only"
then consider that as "1 unit".

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

Re: RITM18130676_Query_PostgreSQL support subscription

[Magnus Hagander]

On Tue, Oct 20, 2020 at 4:42 PM NECSG Purchasing 
wrote:

> Dear Valued Supplier,
>
> May we follow up on our below inquiry please.
>
> Thank you.
>
> Mary Jane Manalo
> Analyst, Vendor Master / Purchasing
> NEC Shared Service Centre
> For Internal NEC Query, please dial 500-63-51- 4004.
> -
>
> To: pgsql-gene...@postgresql.org
> From: purchasing...@necssc.com
> CC: NECSG Purchasing, SSC Purchasing Mailbox
> Date: 2020-10-16 09:47:32
> Subject: RE:RITM18130676_Query_PostgreSQL support subscription
>
> Greetings from NEC,
>
> May we ask for your confirmation about following whether is free license?
>
> PostgreSQL(9.4 - 11)
>

You can find the PostgreSQL license at
https://www.postgresql.org/about/licence/. Yes, it is free.




> PostgreSQL(9.4 - 11) support subscription
>

The PostgreSQL community does not provide subscriptions. You can find
information about the support options, both free and paid, at
https://www.postgresql.org/support/.

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

Re: Feature Requests

[Magnus Hagander]

On Sun, Oct 25, 2020 at 10:26 AM Nikolai Lusan  wrote:

> Hi,
>
> I was wondering where I can see open feature requests. One I would like
> to see is multi-master replication ... I did find a 2016 request that
> was marked as "planned", but to the best of my knowledge it hasn't made
> production yet.
>

PostgreSQL does not have such a thing as "open feature requests".

You can find patches that are currently being worked on at
https://commitfest.postgresql.org/, or on discussions in the list archives.

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

Re: Security issues concerning pgsql replication

[Magnus Hagander]

On Tue, Oct 27, 2020 at 9:52 AM xiebin (F)  wrote:

> Hi,
>
>
>
> I was setting up a master/slave pgsql(version 12.4) cluster using stream
> replication. I found 3 ways to authenticate, but all of them has some
> security issue.
>
>
>
> 1.  Disable authentication.
>
> *cat pg_hba.conf*
>
> *host   all   all0/0   md5*
>
> *host   replication   xie192.168.1.31/32
> <http://192.168.1.31/32>   trust*
>
>
>
> In this case, untrusted users on slave may use pg_basebackup to stole data.
>
>
>
> 2.  Using password.
>
> *cat pg_hba.conf*
>
> *host   all   all0/0   md5*
>
> *host   replication   xie192.168.1.31/32
> <http://192.168.1.31/32>   md5*
>
>
>
> *cat /var/lib/pgsql/.pgpass (on slave)*
>
> *192.168.1.30:5432:xie:mydb:xie*
>
>
>
> In this case, the password is stored unencrypted. File access control may
> help, but it’s not secure enough.
>


Why not? The user who can read that file, can also read the entire database
on the standby node already.


> 3.  Using certificate.
>
> *cat pg_hba.conf*
>
> *host   all   all0/0   md5*
>
> *hostsslreplication   xie192.168.1.31/32
> <http://192.168.1.31/32>   cert clientcert=1*
>
>
>
> *cat postgresql.conf | grep ssl*
>
> *ssl = on*
>
> *ssl_ca_file = 'root.crt'*
>
> *ssl_cert_file = 'server.crt'*
>
> *ssl_crl_file = ''*
>
> *ssl_key_file = 'server.key' *
>
>
>
> *cat recovery.conf*
>
> *primary_conninfo = 'host=192.168.1.30 port=5432 user=xie
> application_name=stream_relication sslrootcert=/tmp/root.crt
> sslcert=/tmp/xie.crt sslkey=/tmp/xie.key'*
>
> *restore_command = ''*
>
> *recovery_target_timeline = 'latest'*
>
> *primary_slot_name = 'rep_slot'*
>
>
>
> The certificates are created by official instructions
> https://www.postgresql.org/docs/12/ssl-tcp.html#SSL-CERTIFICATE-CREATION.
> But the private key is not encrypted.
>
> I noticed in psql 11+ version, a new configuration
> *ssl_passphrase_command* is added, so that encrypted private key can be
> used.
>
> But as far as I know, encrypted private key is not supported in stream
> replication.
>
>
>
> I wonder if there is another way to authenticate in replication? Or does
> pgsql has any plan to support encrypted private key in replication?
>
>
>

PostgreSQL replication supports all authentication methods that
PostgeSQL supports for regular connections, in general. While I haven't
tried it, ssl_passphrase_command should work for this as well as long as it
doesn't require manual user interaction. But it could for example read the
passphrase from a pipe where it's provided off,or from a hardware device.
Do keep in mind that replication might need multiple authentications (for
example if the network disconnects, it has to reconnect).

You can also use for example GSSAPI and Kerberos to do the login. You will
then of course have to figure out how to securely authenticate the postgres
OS user on the standby node to the Kerberos system, but that's doable.
(Though I believe most Kerberos implementations also rely on filesystem
security to protect the tickets, so if you don't trust your filesystem, you
may have a problem with that -- as well as indeed most other authentication
systems -- so you'd have to investigate that within the kerberos system).

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

Re: Security issues concerning pgsql replication

[Magnus Hagander]

On Tue, Oct 27, 2020 at 12:34 PM xiebin (F)  wrote:

> Perhaps you misunderstand me.
>
> It is not user-database, but master-slave interaction that I am
> concerning.
>
> The master-slave replication proceeds continually and requires no manual
> interference.  Both master and slave’s private key are involved, but
> ssl_passphrase_command is only used to parse passphrase of master’s private
> key. Pgsql cannot get slave’s private key automatically, so replication
> failed. I’ve tried and proved it did not work.
>

Ah yeah, you're right. For the client, there is an API for a callback, but
yeah, not a connection string one.

So your method around that would be to use an openssl engine for storage of
it, such as a smartcard (that one you can set in the sslkey parameter).



> I refered to the list of pgsql’s authenticate methods but did not find an
> appropriate one for replication.
>
> https://www.postgresql.org/docs/12/client-authentication.html
>


Replication uses the *same* authentication methods as client connections.
There is no difference.

So you can use for example GSSAPI instead of clientcert, which would move
the responsibility over to your Kerberos system. Then you can set that one
up to require you to manually type in a password or equivalent to get t a
ticket, and configure expiry on that ticket.

//Magnus


*发件人:* Magnus Hagander [mailto:mag...@hagander.net]
>
> *发送时间:* 2020年10月27日 17:00
> *收件人:* xiebin (F) 
> *抄送:* pgsql-gene...@postgresql.org; zhubo (C) ;
> Zhuzheng (IT) ; houxiaowei ;
> yangshaobo (A) ; mapinghu ;
> Songyunpeng ; luoqi (F) 
> *主题:* Re: Security issues concerning pgsql replication
>
>
>
>
>
>
>
> On Tue, Oct 27, 2020 at 9:52 AM xiebin (F)  wrote:
>
> Hi,
>
>
>
> I was setting up a master/slave pgsql(version 12.4) cluster using stream
> replication. I found 3 ways to authenticate, but all of them has some
> security issue.
>
>
>
> 1.  Disable authentication.
>
> *cat pg_hba.conf*
>
> *host   all   all0/0   md5*
>
> *host   replication   xie192.168.1.31/32
> <http://192.168.1.31/32>   trust*
>
>
>
> In this case, untrusted users on slave may use pg_basebackup to stole data.
>
>
>
> 2.  Using password.
>
> *cat pg_hba.conf*
>
> *host   all   all0/0   md5*
>
> *host   replication   xie192.168.1.31/32
> <http://192.168.1.31/32>   md5*
>
>
>
> *cat /var/lib/pgsql/.pgpass (on slave)*
>
> *192.168.1.30:5432:xie:mydb:xie*
>
>
>
> In this case, the password is stored unencrypted. File access control may
> help, but it’s not secure enough.
>
>
>
>
>
> Why not? The user who can read that file, can also read the entire
> database on the standby node already.
>
>
>
> 3.  Using certificate.
>
> *cat pg_hba.conf*
>
> *host   all   all0/0   md5*
>
> *hostsslreplication   xie192.168.1.31/32
> <http://192.168.1.31/32>   cert clientcert=1*
>
>
>
> *cat postgresql.conf | grep ssl*
>
> *ssl = on*
>
> *ssl_ca_file = 'root.crt'*
>
> *ssl_cert_file = 'server.crt'*
>
> *ssl_crl_file = ''*
>
> *ssl_key_file = 'server.key' *
>
>
>
> *cat recovery.conf*
>
> *primary_conninfo = 'host=192.168.1.30 port=5432 user=xie
> application_name=stream_relication sslrootcert=/tmp/root.crt
> sslcert=/tmp/xie.crt sslkey=/tmp/xie.key'*
>
> *restore_command = ''*
>
> *recovery_target_timeline = 'latest'*
>
> *primary_slot_name = 'rep_slot'*
>
>
>
> The certificates are created by official instructions
> https://www.postgresql.org/docs/12/ssl-tcp.html#SSL-CERTIFICATE-CREATION.
> But the private key is not encrypted.
>
> I noticed in psql 11+ version, a new configuration
> *ssl_passphrase_command* is added, so that encrypted private key can be
> used.
>
> But as far as I know, encrypted private key is not supported in stream
> replication.
>
>
>
> I wonder if there is another way to authenticate in replication? Or does
> pgsql has any plan to support encrypted private key in replication?
>
>
>
>
>
> PostgreSQL replication supports all authentication methods that
> PostgeSQL supports for regular connections, in general. While I haven't
> tried it, ssl_passphrase_command should work for this as well as long as it
> doesn't require manual user interaction. But it could for example read the
> passphrase from a pipe where it's provided off,or from a hardware device.
> Do keep in mind that replication might need multiple authentications (

Re: CentOS 7 yum package systemd bug?

[Magnus Hagander]

On Wed, Nov 4, 2020 at 9:45 AM Laurenz Albe  wrote:
>
> On Tue, 2020-11-03 at 09:42 -0600, Doug Whitfield wrote:
> > Unclear to me if this is a systemd bug or a Postgresql 12 bug, so I figured 
> > I would get some thoughts here before reporting in detail.
> >
> > It is pretty simple to reproduce. If you start your standby server with 
> > incorrect username or password
> >  using ’systemctl start postgresql-12’ then systemctl just “hangs”. The 
> > replication issues get logged,
> >  and it isn’t hard to fix, but it doesn’t seem like the appropriate 
> > outcome. If you make a syntax error,
> >  the systemctl knows that you failed. Of course, this is better than having 
> > a normal exit status and
> >  moving on with life only to find out your replication isn’t working, but 
> > it doesn’t seem right.
> >
> > To be clear, I already fixed the issue. I am just wondering if people think 
> > this is a systemd bug
> >  or a PostgresQL bug or it is just a garbage in, garbage out sort of 
> > situation not worth filing anywhere.
>
> That must be the "Type=notify" from the service file.
>
> PostgreSQL notifies systemd as soon as it has started up, which didn't happen 
> in your case.
>
> The idea is that later services that depend on PostgreSQL can rely on it 
> being available.
> I think that is a good thing to have.
> I am no systemd expert, but as far as I know services are started in 
> parallel, so it
> shouldn't block your boot process for other services that don't depend on 
> PostgreSQL.
>

I believe in a hot standby system, we will send the notification to
systemd once we have reached a consistent state and are "ready for
read only connections". If the system is not in that state, and cannot
connect to the primary, then it seems like indeed it gets stuck there
"forever".

I think one can argue whether that's good or bad :)

The PostgreSQL documentation at
https://www.postgresql.org/docs/13/server-start.html talks about this
behaviour, but only notes that crash recovery might be a reason to hit
this timeout. Maybe it needs to also mention replication (and probably
archive recovery)?


> The best place to discuss this would be the "pgsql-pkg-yum" list.

I don't think this is a packaging issue, all the RPMs did was enable
the functionality that's in core postgresql.

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

Re: PANIC: could not write to log file {} at offset {}, length {}: Invalid argument

[Magnus Hagander]

On Thu, Nov 5, 2020 at 3:12 AM Michael Paquier  wrote:
>
> On Wed, Nov 04, 2020 at 01:24:46PM +0100, Andreas Kretschmer wrote:
> >> Any ideas about what is the problem? or anything else I need to check?
> >
> > wild guess: Antivirus Software?
>
> Perhaps not.  To bring more context in here, PostgreSQL opens any
> files on WIN32 with shared writes and reads allowed to have an
> equivalent of what we do on all *nix platforms.  Note here that the
> problem comes from a WAL segment write, which is done after the file
> handle is opened in shared mode.  As long as the fd is correctly
> opened, any attempt for an antivirus software to open a file with an
> exclusive write would be blocked, no?

The problem with AVs generally doesn't come from them opening files in
non-share mode (I've, surprisingly enough, seen backup software that
causes that problem for example). It might happen on scheduled scans
for example, but the bigger problem with AV software has always been
their filter driver software which intercepts both the open/close and
the read/write calls an application makes and "does it's magic" on
them before handing the actual call up to the operating system. It's
completely independent of how the file is opened.

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

Re: JSONB order?

[Magnus Hagander]

On Thu, Nov 5, 2020 at 4:35 PM Tony Shelver  wrote:
>
> I am getting data out of a spreadsheet (Google API) and loading it into a 
> Python 3.8 dict.
> I then dump it to json format. On printing, it's in the correct order:
> {
> "Timestamp": "05/11/2020 17:08:08",
> "Site Name": "SureSecurity Calgary",
> "Last Name": "Shelver",
> "First Name": "Anthony",
> "Middle Name(s)": "",
> "Phone": 555757007,
>  "Person visited": "test",
>  "Body Temperature": 44,
>  "Fever or chills": "No",
>  "Difficulty breathing or shortness of breath": "No",
>  "Cough": "No",
>  "Sore throat, trouble swallowing": "No",
>  "Runny nose/stuffy nose or nasal congestion": "No",
>  "Decrease or loss of smell or taste": "No",
>  "Nausea, vomiting, diarrhea, abdominal pain": "No",
>  "Not feeling well, extreme tiredness, sore muscles":
>  "Yes", "Have you travelled outside of Canada in the past 14 days?": "No",
>  "Have you had close contact with a confirmed or probable case of COVID-19?": 
> "No"
>  }
>
> It's passed to a plpgsql function, using a jsonb parameter variable.
> This insets it into the table, into into a jsonb column.
>
> When looking at what the column contents are, it's been rearranged.  The 
> order always seems to have been rearranged in the same way, as below:
> {
> "Cough": "No",
> "Phone": 757007,
> "Last Name": "Shelver",
> "Site Name": "SureSecurity Calgary",
> "Timestamp": "04/11/2020 17:34:48",
> "First Name": "Anthony",
> "Middle Name(s)": "",
> "Person visited": "Many",
> "Fever or chills": "No",
> "Body Temperature": 44,
> "Sore throat, trouble swallowing": "No",
> "Decrease or loss of smell or taste": "No",
> "Nausea, vomiting, diarrhea, abdominal pain": "No",
> "Runny nose/stuffy nose or nasal congestion": "No",
> "Difficulty breathing or shortness of breath": "No",
> "Not feeling well, extreme tiredness, sore muscles": "No",
> "Have you travelled outside of Canada in the past 14 days?": "No",
> "Have you had close contact with a confirmed or probable case of 
> COVID-19?": "No"
> }
>
> If the order had remained the same, it's child's play to pull the data out 
> and present it in a report, even if the data elements change.
> But...  seen above, the order gets mixed up.
>
> Any ideas?

The json standard declares that the keys in a document are unordered,
and can appear at any order.

In PostgreSQL, jsonb will not preserve key ordering,  as a feature for
efficiency. The plain json datatype will, so if key ordering is
important you should use json instead of jsonb (but you should
probably also not use the json format in general, as it does not
guarantee this)

See https://www.postgresql.org/docs/13/datatype-json.html

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

Re: Query a column with the same name as an operator

[Magnus Hagander]

On Fri, Nov 6, 2020 at 10:00 AM Java Developer  wrote:
>
> Hello,
>
> I am trying to query a column from a table I migrated from my MYSQL
> into POSTGRESQL but I seem to be having issues with a few column names.
>
> the column name cast is also an operator, I think zone may also be a
> problem.
>
> MYSQL: OK
> SELECT id, start_date, local_time, country, city, region, temperature,
> cast, humidity, wind, weather, zone FROM w_records WHERE city =
> 'Edinburgh' AND start_date LIKE '%2020-11-01%' ORDER BY id DESC;
>
> I can run the SELECT * from FROM w_records WHERE city = 'Edinburgh' but
> the above does not work.
>
> Any idea how I can run a query that accept table name that is already a
> Operator?

Yes, cast is a keyword in SQL (not an operator). To use it as a column
name you have to quote it, like
SELECT "cast" FROM test
(and the same when you create the table, or indeed any references to the column)

zone is not, and should be fine.

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

Re: Failed Login Attempts in PostgreSQL

[Magnus Hagander]

On Fri, Nov 13, 2020 at 11:03 AM Jagmohan Kaintura
 wrote:
>
> Hi Team,
> I was looking for a workaround on how we can configure Failed Login attempts 
> feature of Oracle in PostgreSQL.
> The Only requirement is End user shouldn't be allowed to Login after an "n" 
> number of unsuccessful attempts.
>
> Users have the ability to perform all operations on the underlying tables. So 
> we wanted to restrict after doing "n" unsuccessful attempts.
>
> I couldn't get any source anywhere.

You can use fail2ban for example. See for example this thread here
https://www.postgresql.org/message-id/flat/61463e206b7c4c0ca17b03a59e890b78%40lmco.com,
and the config on https://github.com/rc9000/postgres-fail2ban-lockout.
(probably needs some small adaptations, but as a base it should work).

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

Re: Issue upgrading from 9.5 to 13 with pg_upgrade: "connection to database failed: FATAL: database "template1" does not exist"

[Magnus Hagander]

dnf install --excludepkg proj --excludepkg proj-datumgrid postgis30_12
postgis30_12-devel postgis30_12-utils postgis30_12-client postgis30_12-docs

On Fri, Nov 13, 2020 at 7:01 PM Tom Lane  wrote:
>
> Bruce Momjian  writes:
> > On Fri, Nov 13, 2020 at 12:06:34PM -0500, Jeremy Wilson wrote:
> >> Not sure what you mean by this - I’ve installed the postgis packages
for 9.5 and 13 and the extensions are installed and working in 9.5, but I’m
not doing anything but initdb and then pg_upgrade for 13.
>
> > I think he is asking about shared_preload_libraries,
> > local_preload_libraries, and session_preload_libraries.
>
> Yeah, but if Jeremy isn't touching the new cluster's config between
> initdb and pg_upgrade, those won't be set.
>
> I'm kind of baffled at this point.  It seems pretty likely that this
> is related to the v13 postgis problems we've heard a few reports of,
> but the symptoms are a lot different.
>
> Best advice I can give is to go inquire on the postgis mailing lists
> as to whether they've figured out the "free(): invalid pointer"
> issue.  (I assume that dropping postgis from the source DB is not
> an option...)

This is not actually a PostGIS problem, it's a problem with our yum
repository packaging.

The problem is that postgis, through gdal, ended up being linked to two
different versions of proj at the same time.

You can check it by doing:
ldd /usr/pgsql-13/lib/postgis_raster-3.so | grep proj

If that shows up two different "proj" libraries, then you have that same
problem.

In this case, uninstall the OS supplied "proj" library. If that removes
postgis through dependency, let it and then install it with:

dnf install --excludepkg proj --excludepkg proj-datumgrid postgis30_12

as a workaround.

*If* the root cause is the same one, that is...

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

Re: Issue upgrading from 9.5 to 13 with pg_upgrade: "connection to database failed: FATAL: database "template1" does not exist"

[Magnus Hagander]

On Fri, Nov 13, 2020 at 7:10 PM Magnus Hagander  wrote:

> dnf install --excludepkg proj --excludepkg proj-datumgrid postgis30_12
> postgis30_12-devel postgis30_12-utils postgis30_12-client postgis30_12-docs
>
> On Fri, Nov 13, 2020 at 7:01 PM Tom Lane  wrote:
> >
> > Bruce Momjian  writes:
> > > On Fri, Nov 13, 2020 at 12:06:34PM -0500, Jeremy Wilson wrote:
> > >> Not sure what you mean by this - I’ve installed the postgis packages
> for 9.5 and 13 and the extensions are installed and working in 9.5, but I’m
> not doing anything but initdb and then pg_upgrade for 13.
> >
> > > I think he is asking about shared_preload_libraries,
> > > local_preload_libraries, and session_preload_libraries.
> >
> > Yeah, but if Jeremy isn't touching the new cluster's config between
> > initdb and pg_upgrade, those won't be set.
> >
> > I'm kind of baffled at this point.  It seems pretty likely that this
> > is related to the v13 postgis problems we've heard a few reports of,
> > but the symptoms are a lot different.
> >
> > Best advice I can give is to go inquire on the postgis mailing lists
> > as to whether they've figured out the "free(): invalid pointer"
> > issue.  (I assume that dropping postgis from the source DB is not
> > an option...)
>
> This is not actually a PostGIS problem, it's a problem with our yum
> repository packaging.
>
> The problem is that postgis, through gdal, ended up being linked to two
> different versions of proj at the same time.
>
> You can check it by doing:
> ldd /usr/pgsql-13/lib/postgis_raster-3.so | grep proj
>
> If that shows up two different "proj" libraries, then you have that same
> problem.
>
> In this case, uninstall the OS supplied "proj" library. If that removes
> postgis through dependency, let it and then install it with:
>
> dnf install --excludepkg proj --excludepkg proj-datumgrid postgis30_12
>
>
... and that should of course be postgis30_13 if you're on PostgreSQL 13...

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

Re: pg_upgrade from 12 to 13 failes with plpython2

[Magnus Hagander]

On Wed, Nov 18, 2020 at 8:11 AM Marcin Giedz  wrote:
>
> but my question still remains the same - what causes pg_upgrade failure - are 
> functions the reason? what I did was to delete these 2 rows from 
> pg_pltemplate as I thought this may help:
>
> postgres=# delete from pg_pltemplate where tmplname = 'plpython2u';
> DELETE 1
> postgres=# delete from pg_pltemplate where tmplname = 'plpythonu';
> DELETE 1
>
>
> but pg_upgrade still complains about plpython2:
>
> cat loadable_libraries.txt
> could not load library "$libdir/plpython2": ERROR:  could not access file 
> "$libdir/plpython2": No such file or directory
> In database: alaxx
> In database: template1


It's not the template that's interesting, it's the language itself you
need to drop. Log into each database and try to do that, and you will
get something like this if you still have functions using it:
postgres=# DROP LANGUAGE plpython2u;
ERROR:  cannot drop language plpython2u because other objects depend on it
DETAIL:  function testfunc() depends on language plpython2u
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

If you have no functions using it, it will just go away, and once you
have dropped it in both databases you should be good to go.

And of course, if there are functions depending on it, you should
rebuild those on plpython3u before you drop plpython2u (or drop the
functions if they're not in use).

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

Re: Linux package upgrade without dependency conflicts

[Magnus Hagander]

On Thu, Nov 19, 2020 at 2:50 PM Zwettler Markus (OIZ)
 wrote:
>
> We run Postgres 9.6 + 12 Community Edition on RHEL7 which we install directly 
> out of the PGDG channels using RPMs. We also run Patroni installed with RPMs 
> provided by Github.
>
>
>
> Currently we have major dependency conflicts with each quarterly Linux 
> package upgrade (yum upgrade), especially on PostGIS and Patroni.
>
>
>
> I was told that there will be no dependency conflicts anymore when we install 
> Postgres from sourcecode and Patroni with pip.
>
>
>
> Is that correct? Because all Linux packages required by Postgres will 
> continue to be updated.

This is not really a PostgreSQL question, it's more of a RedHat
question I'd say.

In general, no. If you install from source you will have a different
kind of dependency management, and you need to handle all of that
manually. As long as you do, there shouldn't be issues.

That said, what are your dependency conflicts? As long as you're using
the PGDG repositories on RHEL7 it should work without that. There have
been some issues with PostGIS on RHEL8, but I think they are mostly
fine on RHEL7. But if you don't actually show us what your dependency
problems are, we can't tell you how to fix it...

(And why not use Patroni from the PDGD repositories?)


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

Re: archive file "00000001000000000000006F" has wrong size: 67118648 instead of 16777216

[Magnus Hagander]

On Wed, Nov 25, 2020 at 3:02 AM 江川潔  wrote:
>
> Hi,
>
> WAL log recovery was failed on wrong log record size. Could you please advise 
> me what is wrong in the setting ? Any suggestions will be highly appreciated.
>
> Thanks,
> Kiyoshi
>
> postgres.conf:
> wal_level = replica
> archive_mode = on
> archive_command = 'copy "%p" "D:\\BKUP\\pg_archivedir\\PostgreSQL_DEV\\%f"'
>
> recover.conf:
> restore_command = 'copy "D:\\BKUP\\pg_archivedir\\PostgreSQL_DEV" "%p"'

Does your restore command really not have a %f in it anywhere? That
definitely seems wrong... But it does seem to copy some files correct,
which would be weird if it doesn't. Mistake in the report, or is there
something really weird going on with that PostgreSQL_DEV not being a
directory but instead some "magic file"?

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

Re: postgres-10 with FIPS

[Magnus Hagander]

On Wed, Dec 9, 2020 at 5:30 AM Aravindhan Krishnan 
wrote:

> Hi Folks,
>
> Thanks for the responses. Since the underlying knob flip is a paid version
> and we are a SaaS based service provider, this might not align well with
> our requirement and so wanted to build postgres-10 against FIPS compliant
> ssl/crypto. The "pg_config" is of of great help in order to understand the
> build time configure options.
>

Huh? There is *nothing* in PostgreSQL that is a paid version (there is no
such thing, simply), nor in any of our open source packaging.

If you are specifically looking at the Debian or Ubuntu packages, you can
find the full packaging information in the salsa repositories at
https://salsa.debian.org/postgresql/postgresql. It will have all teh
details you need.

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

Re: SV: Problem with ssl and psql in Postgresql 13

[Magnus Hagander]

On Thu, Dec 17, 2020 at 3:36 PM Gustavsson Mikael
 wrote:
>
>
> Hi,
>
> log_connections is on. The ERR message is correct, we do not have an entry 
> for SSL off.
> The question is why psql(13) is trying to connect without ssl?
>
> 2020-12-17T14:25:09.565566+00:00 server INFO [30-1] pgpid=2422778 
> pguser=[unknown] pghost=nnn.nn.n.nnn pgdb=[unknown] pgapp=[unknown] LOG:  
> connection received: host=nnn.nn.n.nnn port=40112
> 2020-12-17T14:25:09.566411+00:00 server ERR [31-1] pgpid=2422778 pguser=kalle 
> pghost=nnn.nn.n.nnn pgdb=postgres pgapp=[unknown] FATAL:  no pg_hba.conf 
> entry for host "nnn.nn.n.nnn", user "kalle", database "postgres", SSL off


By default psql/libpq will fall back to a clear text connection if the
ssl encrypted one failed. Specify sslmode=require (or preferably
higher) in the connection string or set PGSSLMODE=require on the
client to disable that behavior. If you do that, my guess is you will
see a direct connection failure instead of that error?

PostgreSQL 13 did change the default value for minimum tls version to
1.2. But that seems unlikely to be the problem since you get TLS 1.3
when you use the old version...

I assume you're running both the 11 and the 13 client on the same host?

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

Re: SV: Problem with ssl and psql in Postgresql 13

[Magnus Hagander]

That's setting a variable, not a parameter.

You need something like

psql "dbname=postgres user=kalle host=server sslmode=require"

Or
PGSSLMODE=require /usr/bin/pgsql-same-as-you-had-before

//Magnus

On Thu, Dec 17, 2020 at 5:00 PM Gustavsson Mikael
 wrote:
>
> Hi Magnus,
>
>
> The clients 11 and 13 is on the same host.
>
>
> Hmm, I get the same error if I set sslmode=require.
>
>
> $ /usr/pgsql-13/bin/psql -d postgres --set=sslmode=require -Ukalle -hserver -W
> Password:
> psql: error: FATAL:  no pg_hba.conf entry for host "nn.nnn.n.nnn", user 
> "kalle", database "postgres", SSL off
> FATAL:  no pg_hba.conf entry for host "nn.nnn.n.nnn", user "kalle", database 
> "postgres", SSL off
>
> KR Mikael Gustavsson, SMHI
>
>
> 
> Från: Magnus Hagander 
> Skickat: den 17 december 2020 15:52:55
> Till: Gustavsson Mikael
> Kopia: Tom Lane; Kyotaro Horiguchi; pgsql-gene...@postgresql.org; Svensson 
> Peter
> Ämne: Re: SV: Problem with ssl and psql in Postgresql 13
>
> On Thu, Dec 17, 2020 at 3:36 PM Gustavsson Mikael
>  wrote:
> >
> >
> > Hi,
> >
> > log_connections is on. The ERR message is correct, we do not have an entry 
> > for SSL off.
> > The question is why psql(13) is trying to connect without ssl?
> >
> > 2020-12-17T14:25:09.565566+00:00 server INFO [30-1] pgpid=2422778 
> > pguser=[unknown] pghost=nnn.nn.n.nnn pgdb=[unknown] pgapp=[unknown] LOG:  
> > connection received: host=nnn.nn.n.nnn port=40112
> > 2020-12-17T14:25:09.566411+00:00 server ERR [31-1] pgpid=2422778 
> > pguser=kalle pghost=nnn.nn.n.nnn pgdb=postgres pgapp=[unknown] FATAL:  no 
> > pg_hba.conf entry for host "nnn.nn.n.nnn", user "kalle", database 
> > "postgres", SSL off
>
>
> By default psql/libpq will fall back to a clear text connection if the
> ssl encrypted one failed. Specify sslmode=require (or preferably
> higher) in the connection string or set PGSSLMODE=require on the
> client to disable that behavior. If you do that, my guess is you will
> see a direct connection failure instead of that error?
>
> PostgreSQL 13 did change the default value for minimum tls version to
> 1.2. But that seems unlikely to be the problem since you get TLS 1.3
> when you use the old version...
>
> I assume you're running both the 11 and the 13 client on the same host?


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

Re: Restoring 9.1 db from crashed disk/VM

[Magnus Hagander]

On Sat, Jan 2, 2021 at 12:50 PM robert  wrote:
>
> Friends
>
> I hope you had a good start into 2021.
>
>
> I would like to get some advice on how to restore a 9.1 DB, of which I have 
> the
>
> /var/lib/postgresql
>
> with some 20GB salvaged.
>
> Now I find now easily usable 9.1 PostgreSQL installer anymore.
>
> How should I proceed to get that data in a newer PG loaded?
>
> Could I just install a 9.5 and  copy the salvaged folder into
>
> /var/lib/postgresql/9/main
>
>
> Or could I add the folder to my PG 12 installation like:
>
> /var/lib/postgresql/12/main
>  /9/main
>
> and tell PG 12 somehow to read the data for the PG9 folder?

No, you need a PostgreSQL of the same version that you had for the
backups, in this case 9.1. No version outside of 9.1 will be able to
directly use that data folder.

You don't mention what your operating system is, but  baked on the
paths you mention it sounds like a debian based system. If that is it,
then the apt-archive will contain binaries (unsupported of course) for
these old versions. See https://apt-archive.postgresql.org/

If not, then you will have to build from source manually -- the old
versions of PostgreSQL are still available in source form on
https://ftp.postgresql.org/pub/source/

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

Re: Using more than one LDAP?

[Magnus Hagander]

On Wed, Jan 6, 2021 at 3:38 PM Paul Förster  wrote:
>
> Hi,
>
> can I use more than one LDAP server entry in pg_hba.conf? My tests show that 
> only the first one is used.
>
> Example:
>
> ldap1.domain contains user1, user2
> ldap2.another.domain contains user3, user4
>
> All 4 users have an account in a PostgreSQL cluster.
>
> ldap1 is openLDAP and ldap2 is Windows AD. Both naturally have different 
> parameters/options and as such require two different lines in pg_hba.conf.
>
> If I connect as user1 or user2, it works. If I try to connect as user3 or 
> user4, it fails because ldap1 reports the user as non-existent, which is 
> correct for ldap1. But in this case, ldap2 is never asked.
>
> How can I solve this dilemma?

Only if you can create rules in your pg_hba.conf file that knows where
the users are. You can specify multiple servers on one line, but that
only balances across servers that don't work. If a server replies "no"
to a response, PostgreSQL will not move on to the next one. So you
have to make it initially pick the correct rule.

And what would you do if user5 exists in both the two ldap servers?

One hacky way you could do it is create a group role for each server,
maintained by some cron job, that indicates with LDAP server the user
is on. You can then use group matching to pick the correct rule in
pg_hba. It's kind of an ugly hack though..

You'd probably be better off to have a federated ldap server that has
a view of both servers, and use that.

Or even better, since one of your nodes is AD, it speaks Kerberos.
Setting up a Kerberos trust between the two environments would make it
possible to do things like regexp matching on the realm in
pg_ident.conf, and as a bonus you get Kerberos which is a lot more
secure than ldap for auth..  It might have a slightly higher barrier
of entry, but could probably pay off well in a case like this.

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

Re: Using more than one LDAP?

[Magnus Hagander]

On Wed, Jan 6, 2021 at 4:39 PM Paul Förster  wrote:
>
> Hi Magnus,
>
> > On 06. Jan, 2021, at 15:48, Magnus Hagander  wrote:
> >
> > Only if you can create rules in your pg_hba.conf file that knows where
> > the users are. You can specify multiple servers on one line, but that
> > only balances across servers that don't work. If a server replies "no"
> > to a response, PostgreSQL will not move on to the next one. So you
> > have to make it initially pick the correct rule.
>
> that unfortunately is not an option, partly because LDAP and AD use different 
> options and also, as you already mentioned it, if one server says no, it's no.
>
> > And what would you do if user5 exists in both the two ldap servers?
>
> that wouldn't matter as long as user5 exists on the database and can be 
> authenticated by either LDAP.
>
> > One hacky way you could do it is create a group role for each server,
> > maintained by some cron job, that indicates with LDAP server the user
> > is on. You can then use group matching to pick the correct rule in
> > pg_hba. It's kind of an ugly hack though..
>
> that sounds really hacky. ;-)

Yes. But you have a really hacky environment :P


> > You'd probably be better off to have a federated ldap server that has
> > a view of both servers, and use that.
>
> can't do that either. I have no control over both LDAP services. PostgreSQL 
> is just a consumer and I can't make any of the two LDAPs to sync onto each 
> other.

You could have a third LDAP instance that federates the other two.

Another option could be to proxy it through something like FreeRADIUS.
I'm fairly certain it can also move on to a secondary server if the
first one reports login failure.


> > Or even better, since one of your nodes is AD, it speaks Kerberos.
> > Setting up a Kerberos trust between the two environments would make it
> > possible to do things like regexp matching on the realm in
> > pg_ident.conf, and as a bonus you get Kerberos which is a lot more
> > secure than ldap for auth..  It might have a slightly higher barrier
> > of entry, but could probably pay off well in a case like this.
>
> that'd require me to recompile and redistribute the PostgreSQL software. I 
> only have openLDAP compiled into it but no GSSAPI. While this could be 
> possible, it would also mean service interruption, almost not possible in a 
> 24x7 environment. Also, and I'm no expert on this, it would require me to get 
> certificates and configure them, and so on, right?

I assume you're not using any of the standard packagings then, as I
believe they all come with support for GSSAPI. Yet another reason why
it's a good idea to use that :)

And no, gssapi does not use certificates.


> I thought of a pg_ident.conf configuration. In fact, it's more of a prefix 
> change. The complete situation is like this:
>
> ldap1 knows aaa-u1, aaa-u2, and so on
> ldap2 knows bbb-u1, bbb-u2, and so on
>
> So, I thought, I could create a pg_ident.conf like this:
>
> mymap   /^aaa-(.*)$   bbb-\1
>
> Then pg_ctl reload of course. But that doesn't seem to work. Maybe I'm trying 
> something wrong here.

pg_ident only works for authentication methods where the username
comes from the other system, such as with Kerberos. It does not work
for LDAP, where the username is specified in PostgreSQL.


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

Re: LDAP(s) doc misleading

[Magnus Hagander]

On Wed, Jan 6, 2021 at 8:36 AM Paul Förster  wrote:
>
> Hi,
>
> I found what I believe to be misleading in the LDAP documentation:
>
> https://www.postgresql.org/docs/current/auth-ldap.html
>
> It says:
> "ldapscheme
> Set to ldaps to use LDAPS."...
>
> IMHO, it should say:
> "ldapscheme
> Set to ldapscheme to use LDAPS (ldapscheme=ldaps)."...

No, I think this is correct.

"Set to ldaps to use ldaps" means you set it to the value "ldaps" in
order to use ldaps.

I think you missed the "to" in the sentence -- without that one, your
reading of it would make more sense. See also the following parameter,
ldaptls, which uses similar language.

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

Re: Using more than one LDAP?

[Magnus Hagander]

On Thu, Jan 7, 2021 at 10:40 AM Paul Förster  wrote:
>
> Hi Magnus,
>
> > On 06. Jan, 2021, at 16:57, Magnus Hagander  wrote:
> >
> > Yes. But you have a really hacky environment :P
>
> actually not. We have an old LDAP which we want to retire this year. And we 
> also have Windows AD, which offers LDAP. So the idea is to switch the LDAP 
> environments in PostgreSQL. The old LDAP uses aaa-u1, aaa-u2, etc. which are 
> also accounts in the database. But our Windows AD has bbb-u1, bbb-u2, etc. So 
> just switching LDAPs doesn't work. I'd also have to rename all users. Though 
> it's just a one-liner, it would mean that users have to use their new names 
> from one second to the next. But we want a transition phase if that's 
> possible.
>
> > You could have a third LDAP instance that federates the other two.
> >
> > Another option could be to proxy it through something like FreeRADIUS.
> > I'm fairly certain it can also move on to a secondary server if the
> > first one reports login failure.
>
> I can't. I'm no sysadmin and have no rights on systems to install anything 
> except the PostgreSQL software. Also, the network guys wouldn't be too happy. 
> And then, there is a problem introducing new software, which is possible, but 
> can take months for us to get the necessary permissions.

This would be the hacky part of the environment: )



> > I assume you're not using any of the standard packagings then, as I
> > believe they all come with support for GSSAPI. Yet another reason why
> > it's a good idea to use that :)
>
> no, we always compile from source and only what we need. I can build packages 
> with GSSAPI compiled into it but it does require me do have a small service 
> interruption if I install packages with the same PostgreSQL version number, a 
> situation, which I'd like to avoid, if possible.

And this would be the second hacky part of the environment :)


> > And no, gssapi does not use certificates.
>
> that's good news as I'm not really happy about all that certificate stuff. ;-)
>
> > pg_ident only works for authentication methods where the username
> > comes from the other system, such as with Kerberos. It does not work
> > for LDAP, where the username is specified in PostgreSQL.
>
> I don' understand that. The doc says it should work for all external 
> authentication services. Maybe I misread something?...

The docs say "When using an external authentication system such as
Ident or GSSAPI, the name of the operating system user that initiated
the connection might not be the same as the database user (role) that
is to be used."

I think that's a bit of a left-over to when it was really just ident.
First of all it should probably say peer rather than ident, and it's
not actually operating systems that are relevant here.

So I can understand you getting ab it confused by that. but the
property that matter is where the username comes from. In GSSAPI, or
peer, or certificate, etc, the username is provided by the external
system, and the mapping is applied *after* that.

With LDAP authentication, the username is provided by the client, and
is then passed to the external system.

Mapping applies *after* the authentication, which inthe case of LDAP
would be too late to make any difference.

The references to "unix user" and "operating system users" are
probably a leftover from the old days and actually contribute to some
of the confusion I think.

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

Re: pljava": ERROR

[Magnus Hagander]

On Thu, Jan 21, 2021 at 9:04 AM Atul Kumar  wrote:
>
> Hi,
>
> When I was trying to upgrade from postgres 9.5  to postgres 10 using command
>  su - enterprisedb/usr/edb/as10/bin/pg_upgrade -d
> /data/apps/ppas/9.5/data -D  /data/edb/as10/data -U enterprisedb -b
> /usr/ppas-9.5/bin/ -B /usr/edb/as10/bin/ -p 5444 -P 5445 --check
>
> I got below error:
> could not load library "pljava": ERROR:  could not access file
> "pljava": No such file or directory
>
> what is this actual issue and what can be solution of it.

Based on the paths included your problem seems to be with the
proprietary server product from EnterpriseDB, not with PostgreSQL.
For support with that, you should contact the EDB support channels.

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

Re: How to post to this mailing list from a web based interface

[Magnus Hagander]

On Thu, Jan 28, 2021 at 4:27 PM Alvaro Herrera  wrote:
>
> On 2021-Jan-28, Ravi Krishna wrote:
>
> > I am planning to switch to a web based tool to read this mailing list.
>
> That's great.
>
> > While reading is easy via web, how do I post a reply from web.
>
> Yeah, "how" indeed.
>
> > I recollect there use to be a website from where one can reply from web.
>
> The community does not maintain such a service.
>
> There used to be a Gmane archive of this list that you could use to
> post.  Seems it's still online at postgresql-archive.org.  They have a
> "Reply" button and it says to require your account, but given SPF and
> DMARC and other restrictions on email generation, it seems pretty
> uncertain that emails posted that way would work correctly.  I think we
> would even reject such emails if they reached our mailing list servers.

Yeah, given the amount of trouble we've had around that one, I would
strongly advise you not to use it.

Another option is to read it through the web interface and use the
"resend to me" functionality once you want to reply to something, and
then reply to that through your normal email. It's not the most
convenient workflow, but if you mostly read and only very seldom post,
it works.

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

Re: Index created with PGSQL11 on ubuntu 18.04 corrupt with PGSQL11 on ubuntu 20.04

[Magnus Hagander]

On Tue, Feb 2, 2021 at 11:20 AM unilynx  wrote:
>
> I'm using postgresql 11 builds from http://apt.postgresql.org/pub/repos/apt/
> - I've got a database created under Ubuntu 18.04, and recently updated to
> Ubuntu 20.04. These are all docker builds

This is a known problem when upgrading Ubuntu (and most other Linux
distributions, but it depends on which version of course -- btu for
Ubuntu LTS the problem is triggered when going to 20.04). If you have
any indexes on text-type columns, they need to be reindexed.

See https://wiki.postgresql.org/wiki/Locale_data_changes

Note that this is triggered by the Ubuntu upgrade, not by upgrading
PostgreSQL -- that's why it happend even when you keep using the same
PostgreSQL version.

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

Re: How to post to this mailing list from a web based interface

[Magnus Hagander]

On Mon, Feb 15, 2021 at 11:47 PM RaviKrishna  wrote:
>
> > Nable didn't seem to work. I got a bounce iirc.
>
> Not sure what you are doing?  My previous reply a day ago and this reply are
> from Nable with no issues.

Not wtih "no issues".

A lot of email sent from Nabble through the lists end up being spam
flagged and either ignored or blocked by individual subscribers,
because they basically fake the email sender. In this case your mail
provider (yahoo) explicitly instructs recipients to treat it as spam
when you send it through Nabble.

There's enough issues with delivery of emails posted through Nabbel
that we have many times considered simply blocking it so that people
dont' *think* it works well, when it doesn''t. (Since most of the
time, the emails end up in the spam folder, there is no way that you
as a sender end up knowing about it).

Buttom line is that while it may be a good tool for reading, it is
*not* a good tool for posting, at least not until they fix their basic
handling of email.

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

Re: problems with my community account on www.postgresql.org

[Magnus Hagander]

On Wed, Mar 3, 2021 at 12:23 PM Mathias Zarick
 wrote:
>
> Hi there,
>
>
>
> I created a community account some weeks ago, mainly to be able to subscribe 
> to this email lists.
>
> Now I want to edit my subscriptions but I am unable to login. I chose Twitter 
> to be used for authentication.
>
> When I do this again, and click on “Sign in with Twitter” I get into an 
> infinite loop which goes to
>
> https://api.twitter.com/oauth/authorize  where I can click “Authorize app” 
> but I land on api.twitter.com again and so on.
>
> However, when I try to reset my password for my account using the password 
> reset function on
>
> https://www.postgresql.org/account/reset/ I get “Account error”,
>
> “This account cannot change password as it's connected to a third party login 
> site.”
>
>
>
> Can anybody from the mailing list admins help me out here?

Hi!

You seem to have hit a bug in our oauth1 processing (twitter is the
only provider we use oauth1 for, the others use oauth2). I believe
it's been fixed now, please try again.

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

Re: Indexes in JSONB

[Magnus Hagander]

On Tue, Mar 29, 2022 at 7:06 AM Saurav Sarkar 
wrote:

> Hi All,
>
> We use JSONB /NoSQL functionality of PostgreSQL.
>
> One of the column "doc" in our table "Table1" is of type JSONB.
>
> Now the rows in this column "doc" can have different values with different
> schemas.
>
> For e.g values of doc per row will be something like below
>
> ROW1 =  {"id":"1", "name":"abc" }
> ROW2 =  {"id:"2" , "address": "address1"}
> ROW3=   {"id":"3" , "name":"def", "country":"country1" }
>
> So you can see the JSON is changing and keys/schema can be different for
> each rows.
>
> During the course of time indexes will be created for the json keys of the
> JSON values in this column.
> For e.g. on "name", "address" , "country" now. Later I can have another
> key and index like on "continent".
>
> As per postgresql limitations there are limits on the indexes. So I
> understand we can create many indexes.
>
> But obviously I understand creating many indexes will impact write
> performance and space will be utilized.
>
> Would like to know if creating indexes in this manner can create any other
> issues or inputs on the whole topic of indexes in JSONB types.
>

You should not be creating indexes for every individual field if you have
those needs. You should create one index, using GIN and probably
jsonb_path_ops.

Take a look at
https://www.postgresql.org/docs/current/datatype-json.html#JSON-INDEXING

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

Re: Does PGDG apt repository support ARM64?

[Magnus Hagander]

On Fri, Apr 1, 2022 at 8:48 AM Daniele Varrazzo 
wrote:

> > On 3/31/22 18:22, Daniele Varrazzo wrote:
> > > Are arm packages available at all? If so, what is the right procedure
> > > to install them?
>
> On Fri, 1 Apr 2022 at 06:07, Adrian Klaver 
> wrote:
> >
> >  From here:
> >
> > https://apt.postgresql.org/pub/repos/apt/dists/
> >
> > I see them in Debian:
> >
> > Buster on up
>
> Ah, gotcha. Buster is more recent than Stretch.
>

Stretch also goes end of life on June 30 2022, so just a few months away.
You definitely shouldn't be using that.

Buster is also considered the "oldstable" version. You should probably be
using bullseye.  (I assume you're talking about some generic binaries and
not the DEB packages of course -- DEB packages should be built on their
corresponding platform)

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

Re: Does PGDG apt repository support ARM64?

[Magnus Hagander]

On Fri, Apr 1, 2022 at 4:52 PM Daniele Varrazzo 
wrote:

> On Fri, 1 Apr 2022 at 16:28, Magnus Hagander  wrote:
>
> > Stretch also goes end of life on June 30 2022, so just a few months
> away. You definitely shouldn't be using that.
>
> The platform is part of the Python binary packages build chain; they
> are purposely on the old side of the spectrum in order to create
> binaries which can work with as many Linux distributions as possible,
> making use of core libraries ABI compatibility. The rationale is
> available at https://peps.python.org/pep-0513/


Ah yeah, that makes sense. Then buster is likely the better choice, yeah.
(But you do want to get off stretch before it goes into unsupported land)

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

Re: Does PGDG apt repository support ARM64?

[Magnus Hagander]

On Fri, Apr 1, 2022 at 5:08 PM Daniele Varrazzo 
wrote:

> On Fri, 1 Apr 2022 at 17:00, Magnus Hagander  wrote:
> >
> > On Fri, Apr 1, 2022 at 4:52 PM Daniele Varrazzo <
> daniele.varra...@gmail.com> wrote:
> >>
> >> On Fri, 1 Apr 2022 at 16:28, Magnus Hagander 
> wrote:
> >>
> >> > Stretch also goes end of life on June 30 2022, so just a few months
> away. You definitely shouldn't be using that.
> >>
> >> The platform is part of the Python binary packages build chain; they
> >> are purposely on the old side of the spectrum in order to create
> >> binaries which can work with as many Linux distributions as possible,
> >> making use of core libraries ABI compatibility. The rationale is
> >> available at https://peps.python.org/pep-0513/
> >
> > Ah yeah, that makes sense. Then buster is likely the better choice,
> yeah.  (But you do want to get off stretch before it goes into unsupported
> land)
>
> TBH if the PGDG team stops publishing packages for Stretch in 3 months
> it would be an immense pain in the neck, at least until the build
> chain gets updated (which might make the task of building packages
> easier, but it regularly leaves a slice of users unable to use binary
> packages. See https://github.com/psycopg/psycopg/issues/124 for a
> typical case).
>
> Please remember your Python friends!
>

I'm pretty sure they will. However, there is apt-archive that you can
switch to when that happens.  It won't get you any updates, but you will at
least still be able to get the "latest that were. See
https://apt-archive.postgresql.org/.

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

Re: Could not read block 0 in file

[Magnus Hagander]

On Fri, Apr 8, 2022 at 11:06 AM  wrote:

> Hi,
>
> While we are looking for a suitable backup to recover from, I hope this
> community may have some other advice on forward steps in case we cannot
> restore.
>
> RCA: Unexpected shutdown due to critical power failure
>
> Current Issue: The file base/16509/17869 is zero bytes in size.
>
> Additional Information:
> Platform: Windows Server
> PostGres Version: 10.16 (64-bit)
>
> The database does start, and is otherwise functioning and working aside
> from a particular application feature that relies on the lookup of the
> values in the table that was held in the currently zero-bytes data file.
>
> The non-functioning table (ApprovalStageDefinition) is a relatively simple
> table with 5 rows of static data. The contents can easily be recovered with
> a query such as the following for each of the 5 records:
> insert into ApprovalStageDefinition values (1, 'Stage One', 'Stage One');
>
> The error message when running this query is:
> ERROR:  could not read block 0 in file "base/16509/17869": read only 0 of
> 8192 bytes
>
> The file does exist on the file system, with zero bytes, as do the
> associated fsm and vm files.
>
> PostGres does allow us to describe the table:
>  \d ApprovalStageDefinition;
>Table "public.approvalstagedefinition"
>   Column   |  Type  | Collation | Nullable | Default
> ---++---+--+-
>  stageid   | bigint |   | not null |
>  stagename | citext |   | not null |
>  internalstagename | citext |   | not null |
> Indexes:
> "approvalstagedef_pk" PRIMARY KEY, btree (stageid)
> "approvalstagedefinition_uk1" UNIQUE CONSTRAINT, btree (stagename)
> "approvalstagedefinition_uk2" UNIQUE CONSTRAINT, btree
> (internalstagename)
> Check constraints:
> "approvalstagedefinition_internalstagename_c" CHECK
> (length(internalstagename::text) <= 100)
> "approvalstagedefinition_stagename_c" CHECK (length(stagename::text)
> <= 100)
> Referenced by:
> TABLE "approvaldetails" CONSTRAINT "approvaldetails_fk5" FOREIGN KEY
> (stageid) REFERENCES approvalstagedefinition(stageid) ON DELETE CASCADE
> TABLE "currentapprovalstage" CONSTRAINT "currentapprovalst_fk1"
> FOREIGN KEY (stageid) REFERENCES approvalstagedefinition(stageid) ON DELETE
> CASCADE
> TABLE "serviceapprovermapping" CONSTRAINT "serviceapprovermapping_fk4"
> FOREIGN KEY (stageid) REFERENCES approvalstagedefinition(stageid) ON DELETE
> CASCADE
>
> Desired Solution:
> A way to recreate the data file based on the existing schema so that we
> can then insert the required records.
>
> Challenges/Apprehensions:
> I am a PostGres novice, and reluctant to try dropping the table and
> recreating it based on the existing schema as I don’t know what else it may
> break, especially with regards to foreign keys and references.
>
> Any constructive advice would be appreciated.
>
>
As the file is zero bytes, there is no data to recover at the PostgreSQL
level unless you have backups. Your filesystem lost the content of it, so
any recovery must be done at the file system or storage level.  PostgreSQL
can do nothing about a zero bytes file.

The only real option here is to restore from backup, unless you have some
operating system/storage expert at hand who can recover the file from the
filesystem for you.

You can drop and recreate the table, but since your filesystem has already
lost data what's to say it hasn't corrupted other parts of the database as
well? And as you note, since this is underlying storage corruption
PostgreSQL will not be able to do anything about foreign keys etc. You will
have to verify all those manually. If you do trust the rest of the system
(with some reason), drop the three foreign keys, drop and recreate the
table, and then re-add the foreign keys. But having had this type of
fundamental disk corruption, I personally wouldn't trust the rest of the
contents.

If you end up not actually having any backups, I'd suggest:
1. Drop the table
2. pg_dump what's there
3. Re-initialize a new database from initdb (I would also say create a
completely new filesystem underneath it since that's where the corruption
is, if that's easily done)
4. Restore the pg_dump. At this point it will throw errors on any foreign
keys that are "off", and you will have to clean that up manually.

You should also make sure to apply the latest patches for your PostgreSQL
bringing it to version 10.20. But there are AFAIK no bugs in any of those
minors that would cause this type of corruption, so not being properly
updated is not the root cause of your issue.

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

Re: What have I done!?!?!? :-)

[Magnus Hagander]

On Fri, Apr 8, 2022 at 7:57 AM Nikolay Samokhvalov 
wrote:

> On Thu, Apr 7, 2022 at 8:10 AM Jan Wieck  wrote:
>
>> So **IF** Active Record is using that feature, then it can dump any
>> amount of garbage into your PostgreSQL database and PostgreSQL will
>> happily accept it with zero integrity checking.
>>
>
> It's DISABLE TRIGGER ALL
> https://github.com/rails/rails/blob/831031a8cec5bfe59ef653ae2857d4fe64c5698d/activerecord/lib/active_record/connection_adapters/postgresql/referential_integrity.rb#L12
>

A side-note on this, which of course won't help the OP at this point, but
if the general best practice of not running the application with a highly
privileged account is followed, the problem won't occur (it will just fail
early before breaking things). DISABLE TRIGGER ALL requires either
ownership of the table or superuser permissions, none of which it's
recommended that the application run with. Doesn't help once the problem
has occurred of course, but can help avoid it happening in the future.

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

Re: How to get updated order data

[Magnus Hagander]

On Fri, Apr 8, 2022 at 2:26 PM Laurenz Albe 
wrote:

> On Thu, 2022-04-07 at 16:16 +0300, Andrus wrote:
> > Orders are in table
> >
> >  create table order (
> >dokumnr int primary key,
> >packno char(10)
> >);
> > insert into order dokumnr values (123);
> >
> >  One user sets pack number using
> >
> >  update order set packno='Pack1' where dokumnr=123
> >
> >  3 seconds later other user retrieves pack number using
> >
> >  select packno from order where dokumnr=123
> >
> >  However, other user gets null value, not Pack1 as expected. After some
> time later, correct value Pack1 is returned.
> >
> >  How to get updated data from other user immediately?
> >  3 seconds is long time, it is expected that select suld retrieve update
> data.
> >
> >  There are lot of transactions running concurrently. Maybe update
> command is not written to database if second user retrieves it.
> >
> >  How to flush orders table so that current results are returned for
> second user select ?
> >
> >  Using
> >   PostgreSQL 13.2, compiled by Visual C++ build 1914, 64-bit
> >   and psqlODBC driver.
>
> That cannot happen, unless
>
> a) the UPDATE runs in a transaction that hasn't been committed
>
> b) the SELECT is running on a standby server, and there is replication lag
>

There's also:

c) The SELECT runs in a transaction stat *started* before the transaction
that a runs in. (Assuming it then retries with a new transaction later,
that is)

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

Re: What have I done!?!?!? :-)

[Magnus Hagander]

On Fri, Apr 8, 2022 at 3:07 PM Jan Wieck  wrote:

> On 4/8/22 08:58, Magnus Hagander wrote:
> > A side-note on this, which of course won't help the OP at this point,
> > but if the general best practice of not running the application with a
> > highly privileged account is followed, the problem won't occur (it will
> > just fail early before breaking things). DISABLE TRIGGER ALL requires
> > either ownership of the table or superuser permissions, none of which
> > it's recommended that the application run with. Doesn't help once the
> > problem has occurred of course, but can help avoid it happening in the
> > future.
>
> It gets even better further down in that code, where it UPDATEs
> pg_constraint directly. That not only requires superuser but also catupd
> permissions (which are separate from superuser for a reason).
>

Indeed.The fact that's in the code is sadly an indicator of how many
people run their app as superuser :(

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

Re: What have I done!?!?!? :-)

[Magnus Hagander]

On Fri, Apr 8, 2022 at 3:23 PM Perry Smith  wrote:

>
>
> On Apr 8, 2022, at 07:47, Jan Wieck  wrote:
>
> On 4/8/22 01:57, Nikolay Samokhvalov wrote:
>
> On Thu, Apr 7, 2022 at 8:10 AM Jan Wieck  mailto:j...@wi3ck.info >> wrote:
>So **IF** Active Record is using that feature, then it can dump any
>amount of garbage into your PostgreSQL database and PostgreSQL will
>happily accept it with zero integrity checking.
> It's DISABLE TRIGGER ALL
> https://github.com/rails/rails/blob/831031a8cec5bfe59ef653ae2857d4fe64c5698d/activerecord/lib/active_record/connection_adapters/postgresql/referential_integrity.rb#L12
> <
> https://github.com/rails/rails/blob/831031a8cec5bfe59ef653ae2857d4fe64c5698d/activerecord/lib/active_record/connection_adapters/postgresql/referential_integrity.rb#L12
> >
>
>
> Similar poison, same side effect.
>
> Looking further at that code it also directly updates the PostgreSQL
> system catalog. This is a big, red flag.
>
> Why do the Rails developers think they need a sledgehammer like that? It
> seems to be doing that for over 7 years, so it is hard to tell from the
> commit log why they need to disable RI at all.
>
>
> It has been a long time since I’ve done Rails stuff.  What follows is the
> best I can recall but please take it with a grain of salt.
>
> The first problem is that generally Rails does not put constraints in the
> database.  There were others like me who thought that was insane and would
> put constraints in the database — this includes foreign key constraints,
> check constraints, etc.  About the only constraint that could be added into
> the DB using native Rails was the “not null” constraint.
>
> When foreign and other constraints were added, it broke something they
> call “Fixtures” which are present db states that are plopped into the DB
> during testing.  To “fix” that, I (and others) would add this into our code
> base: (I’m adding this to see what you guys think of it — is it safer /
> better or just as insane?)
>
>   def disable_referential_integrity(&block)
> transaction {
>   begin
> execute "SET CONSTRAINTS ALL DEFERRED"
> yield
>   ensure
> execute "SET CONSTRAINTS ALL IMMEDIATE"
>   end
> }
>   end
>

This is perfectly normal code and nothing wrong with it. DEFERRED
constraints are how you are *supposed* to handle such things. It defers the
check of the foreign key to the end of the transaction, but it will still
fail to commit if the foreign key is broken *at that point*. But it lets
you do things like modify multiple tables that refer to each other, and
have the changes only checked when they're all done.


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

Re: What have I done!?!?!? :-)

[Magnus Hagander]

On Fri, Apr 8, 2022 at 3:27 PM Magnus Hagander  wrote:

>
>
> On Fri, Apr 8, 2022 at 3:23 PM Perry Smith  wrote:
>
>>
>>
>> On Apr 8, 2022, at 07:47, Jan Wieck  wrote:
>>
>> On 4/8/22 01:57, Nikolay Samokhvalov wrote:
>>
>> On Thu, Apr 7, 2022 at 8:10 AM Jan Wieck > mailto:j...@wi3ck.info >> wrote:
>>So **IF** Active Record is using that feature, then it can dump any
>>amount of garbage into your PostgreSQL database and PostgreSQL will
>>happily accept it with zero integrity checking.
>> It's DISABLE TRIGGER ALL
>> https://github.com/rails/rails/blob/831031a8cec5bfe59ef653ae2857d4fe64c5698d/activerecord/lib/active_record/connection_adapters/postgresql/referential_integrity.rb#L12
>> <
>> https://github.com/rails/rails/blob/831031a8cec5bfe59ef653ae2857d4fe64c5698d/activerecord/lib/active_record/connection_adapters/postgresql/referential_integrity.rb#L12
>> >
>>
>>
>> Similar poison, same side effect.
>>
>> Looking further at that code it also directly updates the PostgreSQL
>> system catalog. This is a big, red flag.
>>
>> Why do the Rails developers think they need a sledgehammer like that? It
>> seems to be doing that for over 7 years, so it is hard to tell from the
>> commit log why they need to disable RI at all.
>>
>>
>> It has been a long time since I’ve done Rails stuff.  What follows is the
>> best I can recall but please take it with a grain of salt.
>>
>> The first problem is that generally Rails does not put constraints in the
>> database.  There were others like me who thought that was insane and would
>> put constraints in the database — this includes foreign key constraints,
>> check constraints, etc.  About the only constraint that could be added into
>> the DB using native Rails was the “not null” constraint.
>>
>> When foreign and other constraints were added, it broke something they
>> call “Fixtures” which are present db states that are plopped into the DB
>> during testing.  To “fix” that, I (and others) would add this into our code
>> base: (I’m adding this to see what you guys think of it — is it safer /
>> better or just as insane?)
>>
>>   def disable_referential_integrity(&block)
>> transaction {
>>   begin
>> execute "SET CONSTRAINTS ALL DEFERRED"
>> yield
>>   ensure
>> execute "SET CONSTRAINTS ALL IMMEDIATE"
>>   end
>> }
>>   end
>>
>
> This is perfectly normal code and nothing wrong with it. DEFERRED
> constraints are how you are *supposed* to handle such things. It defers the
> check of the foreign key to the end of the transaction, but it will still
> fail to commit if the foreign key is broken *at that point*. But it lets
> you do things like modify multiple tables that refer to each other, and
> have the changes only checked when they're all done.
>
>
Oh, I should add. The code is correct. The name of the function is wrong,
because it doesn't actually disable referential integrity. It only
postpones it.

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

Re: A error happend when I am clone the git repository

[Magnus Hagander]

On Mon, Jun 20, 2022 at 6:48 AM Tom Lane  wrote:

> Adrian Klaver  writes:
> > On 6/19/22 20:33, Wen Yi wrote:
> >> |[beginnerc@fedora Research]$ git clone
> >> ||https://git.postgresql.org/git/postgresql.git
> >> <https://git.postgresql.org/git/postgresql.git>|
> >> |Cloning into 'postgresql'...|
> >> |remote: Enumerating objects: 30747, done.|
> >> |remote: Counting objects: 100% (30747/30747), done.|
> >> |remote: Compressing objects: 100% (13431/13431), done.|
> >> |error: RPC failed; curl 92 HTTP/2 stream 3 was not closed cleanly
> >> before end of the underlying stream|
>
> > I have to believe it is related to bleeding edge OS Fedora 36 in
> > combination with latest Git 2.36.1.
>
> No, I think it's more about this:
>
> >> I check the download speed,that is about 220kb/s.
>
> I've seen this failure multiple times on very slow machines.
> I think there's some sort of connection timeout somewhere in
> the git.postgresql.org infrastructure, causing a "git clone"
> that takes more than a couple of minutes to fail.  I've
> complained about it before, but we've not isolated the cause.
>

The last time we debugged it pretty much the only conclusion we managed to
come to I think was that it is *not* in the git.postgresql.org
infrastructure. I tried it from *many* different locations and it worked
fine from all of them, even when artificially slowing it down to something
much slower.

So yes, there is *something*, but it's not with in the pg.org
infrastructure.

One thing we got to work that time, I think, was to run:
git config --global http.version HTTP/1.1

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

Re: Table space not returned to the OS ?

[Magnus Hagander]

On Mon, Jun 27, 2022 at 11:30 AM Florents Tselai 
wrote:

> Hi,
>
> A few months back (October) I had upgraded a Postgres instance from v12 —>
> 14.
>
> The database disk size under /var/lib/postgresql/12 was around 800GB+ back
> then.
> Note, that IIRC I had used hard-linking during the upgrade.
>
> In the database itself, lots of things have changed since.
> In fact, that database itself has been dropped at some point and restored
> from a backup.
>
> As I was running out of disk space, I started investigating and found out
> that
>
> /var/lib/postgresql/12/main/base/16385  —>  886GB+
> /var/lib/postgresql/14 —> 400GB
>
> The last modification date on that directory (../12/) appears to be around
> a month ago,
> When the table with relied 16385 was in fact dropped.
>
> Now, In my update scripts (I use this db as an OLAP) I occasionally run
> VACUUM.
>
> Is it weird that the 886GB space hasn’t been returned to the OS yet?
>
> What’s the safest way to return it to the OS manually?
>
>
When you use hardlinks in the upgrade all the files remain in the old
directory when they are removed from the new one such as when you drop a
relation. it is there for emergency recoveries. It's only the contents of
the files that's "mirrored", not the existance.

It looks like you didn't actually delete the old cluster, which you are
supposed to do once you have verified that the new one works. This looks
like a debian/ubuntu system, which means you probably forgot to run
"pg_dropcluster 12 main"? Or if it's not a debian cluster, the equivalent
of that which results in removing the data directory for 12 along with any
configuration files it has elsewhere.

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

Re: Different sort result between PostgreSQL 8.4 and 12.5

[Magnus Hagander]

On Mon, Jun 27, 2022 at 1:31 PM gzh  wrote:

> Hi,
>
>
> I have had a Perl Website working for 7 years and have had no problems
> until a few weeks ago I replaced my database server with a newer one.
>
>
> Database server (old): PostgreSQL 8.4 32bit
>
> Database server (new): PostgreSQL 12.5 64bit
>
>
> I run following sql in PostgreSQL 8.4 and PostgreSQL 12.5, it returns
> different sort results.
>
>
> --PostgreSQL 8.4
>
> ---
>
> pg_db=# select ascid from test_order where oo_m.ascid in ('"!
> ascid"','"001"') order by ascid;
>
>ascid
>
> ---
>
>  "! ascid"
>
>  "001"
>
> (2 rows)
>
>
> --PostgreSQL 12.5
>
> ---
>
> pg_db=# select ascid from test_order where oo_m.ascid in ('"!
> ascid"','"001"') order by ascid;
>
>ascid
>
> ---
>
>  "001"
>
>  "! ascid"
>
> (2 rows)
>
>
> What is the reason for this and is there any easy way to maintain
> compatibility?
>
>
Are these two really running on the same operating system?

This looks a lot like the locale changes included in newer versions of
glibc, and is in that case dependent on an upgrade of the operating system,
not an upgrade of PostgreSQL. See
https://wiki.postgresql.org/wiki/Locale_data_changes for details.

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

Re: Table space not returned to the OS ?

[Magnus Hagander]

On Mon, Jun 27, 2022 at 12:01 PM Laurenz Albe 
wrote:

> On Mon, 2022-06-27 at 11:38 +0200, Magnus Hagander wrote:
> > On Mon, Jun 27, 2022 at 11:30 AM Florents Tselai <
> florents.tse...@gmail.com> wrote:
> > > A few months back (October) I had upgraded a Postgres instance from
> v12 —> 14.
> > >
> > > The database disk size under /var/lib/postgresql/12 was around 800GB+
> back then.
> > > Note, that IIRC I had used hard-linking during the upgrade.
> > >
> > > As I was running out of disk space, I started investigating and found
> out that
> > >
> > > /var/lib/postgresql/12/main/base/16385  —>  886GB+
> > > /var/lib/postgresql/14 —> 400GB
> >
> > It looks like you didn't actually delete the old cluster, which you are
> supposed
> > to do once you have verified that the new one works.
>
> I think that it should be done earlier than that, namely immediately after
> running
> pg_upgrade.  Once you have started the PostgreSQL 14 server (to verify
> that it works),
> you can no longer use the old cluster.
> Yes, the control file is crippled, but in my opinion, the earlier you
> delete the old
> cluster, the safer.
>

I'd say there is still some recoverable data in the old cluster files, even
if you can't just start up the cluster in it. But yes, it comes down to how
you define "verified that the new one works" to some level.

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

Re: Are ZFS snapshots unsafe when PGSQL is spreading through multiple zpools?

[Magnus Hagander]

On Sun, Jan 15, 2023 at 8:18 PM HECTOR INGERTO 
wrote:

> Hello everybody,
>
>
>
> I’m using PostgreSQL on openZFS. I use ZFS snapshots as a backup +
> hotspare method.
>
>
>
> From man zfs-snapshot: “Snapshots are taken atomically, so that all
> snapshots correspond to the same moment in time.” So if a PSQL instance is
> started from a zfs snapshot, it will start to replay the WAL from the last
> checkpoint, in the same way it would do in a crash or power loss scenario. So
> from my knowledge, ZFS snapshots can be used to rollback to a previous
> point in time. Also, sending those snapshots to other computers will allow
> you to have hotspares and remote backups. If I’m wrong here, I would
> appreciate being told about it because I’m basing the whole question on
> this premise.
>
>
>
> On the other hand, we have the tablespace PGSQL feature, which is great
> because it allows “unimportant” big data to be written into cheap HDD and
> frequently used data into fast NVMe.
>
>
>
> So far, so good. The problem is when both ideas are merged. Then,
> snapshots from different pools are NOT atomical, snapshot on the HDD pool
> isn’t going to be done at the same exact time as the one on the SSD pool,
> and I don’t know enough about PGSQL internals to know how dangerous this
> is. So here is where I would like to ask for your help with the following
> questions:
>
>
>
> First of all, what kind of problem can this lead to? Are we talking about
> potential whole DB corruption or only the loss of a few of the latest
> transactions?
>

Silent data corruption. *not* just losing your latest transaction.



> In second place, if I’m initializing a corrupted PGSQL instance because
> ZFS snapshots are from different pools and slightly different times, am I
> going to notice it somehow or is it going to fail silently?
>

Silent. You might notice at the application level. Might.



> In third and last place, is there some way to quantify the amount of risk
> taken when snapshotting a PGSQL instance spread across two (or more)
> different pools?
>
>
>
"Don't do it".

If you can't get atomic snapshots, don't do it, period.

You can use them together with a regular online backup. That is
pg_start_backup() //  // pg_stop_backup()
together with log archiving. That's a perfectly valid method. But you
cannot and should not rely on snapshots alone.

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

Re: Are ZFS snapshots unsafe when PGSQL is spreading through multiple zpools?

[Magnus Hagander]

On Sun, Jan 15, 2023 at 10:57 PM HECTOR INGERTO 
wrote:

>
>
> > But you cannot and should not rely on snapshots alone
>
>
>
> That’s only for non atomic (multiple pools) snapshots. Isn’t?
>

Right. For single-filesystem installs it should be fine. Just make sure it
has both the data and the WAL directories in the same one.




> If I need to rely only on ZFS (automated) snapshots, then the best option
> would be to have two DB? Each one in each own pool. One HDD DB and one SSD
> DB. Then, the backend code should know on which DB the requested data is.
>

 You could. I wouldn't -- I would set it up to use proper backups instead,
maybe using snapshots as the infrastructure. That gives you other
advantages as well, like being able to do PITR. It's a little more to set
up, but I'd say it's worth it.

//Magnus