Re: Writing WAL files

[David G. Johnston]

On Sunday, October 4, 2020, Robert Inder  wrote:

> than shipping an empty file every few minutes?
>

The file is not empty.  We’re talking 16 megabytes in a default setup...

David J.

Re: Can I get some PostgreSQL developer feedback on these five general issues I have with PostgreSQL and its ecosystem?

[Achilleas Mantzios]

On 2/10/20 2:08 π.μ., tutilu...@tutanota.com wrote:


On Thu, Sep 24, 2020 at 10:40 PM mailto:tutilu...@tutanota.com>> wrote:


Well not partial as in incremental. Instead dump only some
portion of the schema with or without its associated data.

It's funny that you should bring that up, considering how it
was one of my points... See the point about pg_dump's bug on
Windows.


And you seem to have ignored the fact that one of the core
developers pointed out that it likely isn't a pg_dump bug - if
your terminal is using the same locale as the database, it should
have no difficulty dealing with the characters you are having
trouble with.  It seems likely that you simply need to learn how
to get your terminal set up correctly for it to work.

Yeah, this isn't rude or insulting at all...

Funny how my "incorrectly set up terminal" works perfectly for all 
other programs and my own test scripts, but not for pg_dump 
specifically. And only when using "special" characters. As already 
pointed out multiple times, in great detail, to deaf ears. Very 
interesting how you can manage to twist and bend that into it still 
somehow being "my fault". Because of course it cannot be pg_dump's 
fault. Absolutely not. It is unthinkable. It's the "rude user"'s fault 
who had the audacity to point out yet another PG bug which more than 
likely won't *ever* be fixed, as it's not even recognized, much less 
cared about. Probably because they *want* PostgreSQL to be crippled on 
Windows, judging by the responses in the past and how incredibly 
broken the joke of an installer is.


You should call it "Linux software with minimal pre-alpha Windows 
support" instead of pretending that it's cross-platform, and that goes 
for many FOSS projects as well which think exactly the same as you. 
The fact that I still use this garbage OS (Windows) speaks volumes of 
how incredibly crappy Linux is, which is utterly *unusable*.


But of course I should be grateful no matter what because it doesn't 
cost money. Because my time and energy is worthless. And the 
competition is "even worse", so that means I cannot point out any 
fault, ever, no matter how serious or how easily it could be fixed. I 
should just shut up and thank everyone for insulting me through 
carelessness and words. Or "fix it myself", because that's obviously 
an option as I haven't done it so far...


I did read the rest of your e-mail, but it would be pointless to reply 
to it as you clearly have the mentality that everyone should dedicate 
their lives to configuring a database and buying books instead of 
using it, because everyone should be core developers and everything 
must always be cryptic and difficult and blablabla. I'm sick of this 
attitude, and especially of being called "rude" by such 
rude-beyond-words people.


It would be refreshing to hear your be honest for once and just admit 
that you *want* it to be difficult. You *like* that there's a high 
threshold and it makes you feel superior to exclude "dumb" people who 
can't figure out all these cryptic (and downright broken) things. I 
truly believe that this is the reason for a lot of "weird" things 
which seem to make no sense on the surface.



I'd say take your time, take some deep breaths and decide that's good 
for you. Back in 2004 and after 3 yrs of full production software with 
postgresql someone from the mailing list (he's also in this thread!!) 
called me a "newbie", and I immediately started looking for 
alternatives, only to find out simply that there was no better DB 
software in the market/world back then (and still as we speak). So I 
stayed with PGSQL and wrote what I believe the best non-tcp-ip DB 
replication solution for marine and shipping business (over satellite), 
which still thrives today as far as easiness, automation, completeness, 
correctness and cost are concerned.


+ I discover every day that I am still a newbie, after 20 yrs with 
postgresql. This is not personal, this is about being successful in the 
long run, one should weigh his options and act accordingly. It took 
me/us a long time before we spent a single penny on someone to write or 
fix code that would work for us, but this moment eventually came, there 
is a roof when going with community software. That roof came for us much 
much later since we begun using PostgreSQL. Value for money is so hard 
to beat. I have seen the code by our MS SQL partners , some interesting 
and serious things happening there but when they listen what stock free 
pgsql can do they just freak out (the ones who understand).


So my advice, tell your CEO's the true potential of this technology and 
maybe show them some stats, some results of others, some numbers. Put 
them side by side with the rest of serious solutions and then decide.

Re: Can't query system tables during transaction

[Igor Korot]

Hi,

On Sun, Oct 4, 2020 at 3:30 PM Tom Lane  wrote:
>
> Igor Korot  writes:
> > I'm trying to execute following:
>
> > SELECT 1 FROM pg_class c, pg_namespace n WHERE n.oid = c.relnamespace
> > AND c.relname = 'foo' AND n.nspname = public;
>
> I suppose you meant to put quotes around 'public'?

I suppose so as well. ;-)

>
> > I'm getting the following error:
> > ERROR:  current transaction is aborted, commands ignored until end of
> > transaction block
>
> This has nothing to do with the current command, but with failure
> of some previous command in the transaction.

Thank you.
I will try to track down the error.

>
> regards, tom lane

Re: Can't query system tables during transaction

[Tom Lane]

Igor Korot  writes:
> I'm trying to execute following:

> SELECT 1 FROM pg_class c, pg_namespace n WHERE n.oid = c.relnamespace
> AND c.relname = 'foo' AND n.nspname = public;

I suppose you meant to put quotes around 'public'?

> I'm getting the following error:
> ERROR:  current transaction is aborted, commands ignored until end of
> transaction block

This has nothing to do with the current command, but with failure
of some previous command in the transaction.

regards, tom lane

Re: Can't query system tables during transaction

[Adrian Klaver]

On 10/4/20 1:14 PM, Igor Korot wrote:

Hi, ALL,
I'm trying to execute following:

SELECT 1 FROM pg_class c, pg_namespace n WHERE n.oid = c.relnamespace
AND c.relname = 'foo' AND n.nspname = public;

inside the transaction.

I'm getting the following error:

ERROR:  current transaction is aborted, commands ignored until end of
transaction block


No it means another statement before this one threw an error and the 
transaction needs to be rolled back. Something like this:


track_stocks(5442)=> begin ;
BEGIN
track_stocks(5442)=> SELECT 1 FROM pg_class c, pg_namespace n WHERE 
n.oid = c.relnamespace

AND c.relname = 'stock-info' AND n.nspname = public;
ERROR:  column "public" does not exist
LINE 2: AND c.relname = 'stock-info' AND n.nspname = public;
 ^
track_stocks(5442)=> SELECT 1 FROM pg_class c, pg_namespace n WHERE 
n.oid = c.relnamespace

AND c.relname = 'stock-info' AND n.nspname = 'public';
ERROR:  current transaction is aborted, commands ignored until end of 
transaction block


track_stocks(5442)=> rollback ;
ROLLBACK

And now the correct query(Note the quoted schema name):

track_stocks(5442)=> begin ;
BEGIN
track_stocks(5442)=> SELECT 1 FROM pg_class c, pg_namespace n WHERE 
n.oid = c.relnamespace

AND c.relname = 'stock-info' AND n.nspname = 'public';
 ?column?
--
(0 rows)



Does this mean I can't query system tables during the transaction?
What is the problem here if it's not and how do I find out the reason?
And if it is - how to work around it?

I can probably commit it and start a new transaction, but I fear I will
have the same issue there...

Thank you.

If it matters - I'm working with C++ and libpq.





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

Can't query system tables during transaction

[Igor Korot]

Hi, ALL,
I'm trying to execute following:

SELECT 1 FROM pg_class c, pg_namespace n WHERE n.oid = c.relnamespace
AND c.relname = 'foo' AND n.nspname = public;

inside the transaction.

I'm getting the following error:

ERROR:  current transaction is aborted, commands ignored until end of
transaction block

Does this mean I can't query system tables during the transaction?
What is the problem here if it's not and how do I find out the reason?
And if it is - how to work around it?

I can probably commit it and start a new transaction, but I fear I will
have the same issue there...

Thank you.

If it matters - I'm working with C++ and libpq.

Re: Writing WAL files

[Robert Inder]

On Sun, 4 Oct 2020 at 20:52, Alvaro Herrera  wrote:

>
> This is on purpose; archiving WAL files that contain nothing is pure
> wastage of good electrons.

Seriously?  Oh, holy 


> I suggest that in PG12 you can monitor the
> "lag" of a standby server more directly by looking at columns write_lag,
> flush_lag, replay_lag in the pg_stat_replication view.


And are those things updated when there are no changes to the master
database?
If so, can anyone make the case that continually checking and updating them
(how often?) wastes fewer electrons than shipping an empty file every few
minutes?

Or are they only measured when something is updated?

If I upgrade/install/reconfigure/restart something, I want to know that I
haven't broken the sync.

Will looking at the replay_lag (where?  master?  standby?) tell me that the
sync is still good?
Or will they capture the last sync. operation, and so only tell me what I
need to know if I do some kind of database operation?

And if I have to do some kind of database operation, I may as well stick
wiht the current arrangement,
since that operation would force a WAL file transfer anyway...

(You'll need to
> change your configuration so that it uses streaming replication instead
> of pg_standby and rsync, but that's far more convenient so it's a good
> change anyway.)
>

Maybe, but it's forcing me to spend time understanding stuff that I really
don't want to know about.

Robert.

-- 
Robert Inder,0131 229 1052 / 07808 492
213
Interactive Information Ltd,   3, Lauriston Gardens, Edinburgh EH3 9HH
Registered in Scotland, Company no. SC 150689
   Interactions speak louder than
words

Re: Writing WAL files

[Alvaro Herrera]

Hello Robert

On 2020-Oct-04, Robert Inder wrote:

> One of the things I like about the old PGSQL 9 setup is that it generates
> and ships a WAL file every few minutes, even if nothing has happened in the
> database.
> I find it re-assuring to be able to see WAL files arriving and being
> processed without problem even when the live system was idle.
> 
> But I cannot get PGSQL 12 to do this.  It only writes (and thus ships) WAL
> files when something happens in the database.
> If the database is idle, it simply does not write any WAL files.

This is on purpose; archiving WAL files that contain nothing is pure
wastage of good electrons.  I suggest that in PG12 you can monitor the
"lag" of a standby server more directly by looking at columns write_lag,
flush_lag, replay_lag in the pg_stat_replication view.  (You'll need to
change your configuration so that it uses streaming replication instead
of pg_standby and rsync, but that's far more convenient so it's a good
change anyway.)

Re: Writing WAL files

[Adrian Klaver]

On 10/4/20 10:30 AM, Robert Inder wrote:



On Sun, 4 Oct 2020 at 18:01, Adrian Klaver > wrote:


On 10/4/20 9:54 AM, Robert Inder wrote:
 > I am moving a database from PSQL 9 (!) on CentOS 6 to PSQL 12 on
CentOS 7

It would help to know what the x in 9.x is? Before version 10 of
Postgres, the second number denoted a major version.


9.4.
Moving to 12.4.



Should have added to previous post:

Did you restart the server after you made the changes?


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

Re: Writing WAL files

[Adrian Klaver]

On 10/4/20 10:30 AM, Robert Inder wrote:



On Sun, 4 Oct 2020 at 18:01, Adrian Klaver > wrote:


On 10/4/20 9:54 AM, Robert Inder wrote:
 > I am moving a database from PSQL 9 (!) on CentOS 6 to PSQL 12 on
CentOS 7

It would help to know what the x in 9.x is? Before version 10 of
Postgres, the second number denoted a major version.


9.4.
Moving to 12.4.


Well I'm going to say it has to do with this:

https://www.postgresql.org/docs/10/release-10.html

E.15.3.1.9.1. Write-Ahead Log (WAL)
Prevent unnecessary checkpoints and WAL archiving on otherwise-idle 
systems (Michael Paquier)


Because that is when this:

". (Increasing checkpoint_timeout will reduce unnecessary checkpoints on 
an idle system.)"


disappeared from the archive_timeout docs:

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

vs

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

Someone else will have to fill in the details.


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

Re: Writing WAL files

[Robert Inder]

On Sun, 4 Oct 2020 at 18:01, Adrian Klaver 
wrote:

> On 10/4/20 9:54 AM, Robert Inder wrote:
> > I am moving a database from PSQL 9 (!) on CentOS 6 to PSQL 12 on CentOS 7
>
> It would help to know what the x in 9.x is? Before version 10 of
> Postgres, the second number denoted a major version.
>

9.4.
Moving to 12.4.



>
> >
> > I have a pair of servers -- one live, one standby.
> > The live server defines an archive_command as "rsync" to shift WAL
> > files to the standby server,
> > The standby server uses "pg_standby" to monitor and process incoming WAL
> > files.
> > I believe this is all very vanilla, and indeed changes made in the live
> > database are duly shipped to the standby.
> >
> > BUT...
> >
> > One of the things I like about the old PGSQL 9 setup is that it
> > generates and ships a WAL file every few minutes, even if nothing has
> > happened in the database.
> > I find it re-assuring to be able to see WAL files arriving and being
> > processed without problem even when the live system was idle.
> >
> > But I cannot get PGSQL 12 to do this.  It only writes (and thus ships)
> > WAL files when something happens in the database.
> > If the database is idle, it simply does not write any WAL files.
> >
> > I thought I would get WAL files written from an idle database if, in
> > postgresql.conf, I set "archive_timeout" to 120.
> >
> > And I've tried setting "checkpoint_timeout" to 90s,
> >
> > But to no avail.  No WAL files are written unless the database changes.
> >
> > So what am I missing?  How CAN I get postgresql 12 to write
> > "unnecessary" WAL files every couple of minutes?
> >
> > Robert.
> >
> > --
> > Robert Inder,0131 229 1052 / 07808
> > 492 213
> > Interactive Information Ltd,   3, Lauriston Gardens, Edinburgh EH3 9HH
> > Registered in Scotland, Company no. SC 150689
> > Interactions speak louder
> > than words
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>
>

-- 
Robert Inder,0131 229 1052 / 07808 492
213
Interactive Information Ltd,   3, Lauriston Gardens, Edinburgh EH3 9HH
Registered in Scotland, Company no. SC 150689
   Interactions speak louder than
words

Re: Writing WAL files

[Adrian Klaver]

On 10/4/20 9:54 AM, Robert Inder wrote:

I am moving a database from PSQL 9 (!) on CentOS 6 to PSQL 12 on CentOS 7


It would help to know what the x in 9.x is? Before version 10 of 
Postgres, the second number denoted a major version.




I have a pair of servers -- one live, one standby.
The live server defines an archive_command as "rsync" to shift WAL 
files to the standby server,
The standby server uses "pg_standby" to monitor and process incoming WAL 
files.
I believe this is all very vanilla, and indeed changes made in the live 
database are duly shipped to the standby.


BUT...

One of the things I like about the old PGSQL 9 setup is that it 
generates and ships a WAL file every few minutes, even if nothing has 
happened in the database.
I find it re-assuring to be able to see WAL files arriving and being 
processed without problem even when the live system was idle.


But I cannot get PGSQL 12 to do this.  It only writes (and thus ships) 
WAL files when something happens in the database.

If the database is idle, it simply does not write any WAL files.

I thought I would get WAL files written from an idle database if, in 
postgresql.conf, I set "archive_timeout" to 120.


And I've tried setting "checkpoint_timeout" to 90s,

But to no avail.  No WAL files are written unless the database changes.

So what am I missing?  How CAN I get postgresql 12 to write 
"unnecessary" WAL files every couple of minutes?


Robert.

--
Robert Inder,                                    0131 229 1052 / 07808 
492 213

Interactive Information Ltd,   3, Lauriston Gardens, Edinburgh EH3 9HH
Registered in Scotland, Company no. SC 150689
                                            Interactions speak louder 
than words



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

Writing WAL files

[Robert Inder]

I am moving a database from PSQL 9 (!) on CentOS 6 to PSQL 12 on CentOS 7

I have a pair of servers -- one live, one standby.
The live server defines an archive_command as "rsync" to shift WAL
files to the standby server,
The standby server uses "pg_standby" to monitor and process incoming WAL
files.
I believe this is all very vanilla, and indeed changes made in the live
database are duly shipped to the standby.

BUT...

One of the things I like about the old PGSQL 9 setup is that it generates
and ships a WAL file every few minutes, even if nothing has happened in the
database.
I find it re-assuring to be able to see WAL files arriving and being
processed without problem even when the live system was idle.

But I cannot get PGSQL 12 to do this.  It only writes (and thus ships) WAL
files when something happens in the database.
If the database is idle, it simply does not write any WAL files.

I thought I would get WAL files written from an idle database if, in
postgresql.conf, I set "archive_timeout" to 120.

And I've tried setting "checkpoint_timeout" to 90s,

But to no avail.  No WAL files are written unless the database changes.

So what am I missing?  How CAN I get postgresql 12 to write "unnecessary"
WAL files every couple of minutes?

Robert.

-- 
Robert Inder,0131 229 1052 / 07808 492
213
Interactive Information Ltd,   3, Lauriston Gardens, Edinburgh EH3 9HH
Registered in Scotland, Company no. SC 150689
   Interactions speak louder than
words