SV: Log files polluted with permission denied error messages after every 10 seconds

[Niels Jespersen]

>
>
>Fra: Andrus 
>Sendt: 8. marts 2021 23:24
>Emne: Re: Log files polluted with permission denied error messages after every 
>10 seconds
>
>Permission denied error occur in  Windows. Debian logs doesnt contain it.
>In Windows also pg_database_size('mydb')  and similar ones fail frequently 
>with permission denied error.

Any hints in Windows event viewer? Events occurring at the same time showing up 
there.

>I got information about r1soft idera backup client used. It runs only once per 
>night for 16 minutes and compares disk blocks to find changed blocks to 
>backup. So it should not cause permission denied errors after every 10 seconds.
>Andrus.
>

Re: Need help with clarification on stored procedure support in PostGreSQL database

[David G. Johnston]

On Mon, Mar 8, 2021 at 9:41 PM Tom Lane  wrote:

> Guyren Howe  writes:
> > This seems like an important consideration. I’ve spent 10 minutes
> searching the documentation for PG 11 and can’t find where it is
> documented. Perhaps it should be made more prominent?
>
> https://www.postgresql.org/docs/current/xproc.html
>
>
CREATE FUNCTION links into 37.3 but CREATE PROCEDURE doesn't link into 37.4

Even if this may not be sufficient it seems reasonable to at least remain
consistent.  I suspect most people start at CREATE, not "Server
Programming".

David J.

Re: Need help with clarification on stored procedure support in PostGreSQL database

[Guyren Howe]

I think that’s burying the lede a bit. Still, unless the “Extending SQL 
Section” acquired an Overview section, I’m not sure where else you’d put it.
On Mar 8, 2021, 20:41 -0800, Tom Lane , wrote:
> Guyren Howe  writes:
> > This seems like an important consideration. I’ve spent 10 minutes searching 
> > the documentation for PG 11 and can’t find where it is documented. Perhaps 
> > it should be made more prominent?
>
> https://www.postgresql.org/docs/current/xproc.html
>
> regards, tom lane

Re: Need help with clarification on stored procedure support in PostGreSQL database

[Tom Lane]

Guyren Howe  writes:
> This seems like an important consideration. I’ve spent 10 minutes searching 
> the documentation for PG 11 and can’t find where it is documented. Perhaps it 
> should be made more prominent?

https://www.postgresql.org/docs/current/xproc.html

regards, tom lane

Re: Need help with clarification on stored procedure support in PostGreSQL database

[Guyren Howe]

This seems like an important consideration. I’ve spent 10 minutes searching the 
documentation for PG 11 and can’t find where it is documented. Perhaps it 
should be made more prominent?
On Mar 8, 2021, 16:53 -0800, raf , wrote:
> On Mon, Mar 08, 2021 at 06:31:56PM -0500, Tom Lane  wrote:
>
> > raf  writes:
> > > Is there any important different between a stored procedure and
> > > a stored function that returns void? I've been using functions
> > > for everything but calling them procedures in my head (even though
> > > most of them do return a value or result set).
> >
> > A procedure can commit or rollback a transaction (and then start
> > a new one).
> >
> > regards, tom lane
>
> thanks.
>
> cheers,
> raf
>
>
>

Re: Need help with clarification on stored procedure support in PostGreSQL database

[raf]

On Mon, Mar 08, 2021 at 06:31:56PM -0500, Tom Lane  wrote:

> raf  writes:
> > Is there any important different between a stored procedure and
> > a stored function that returns void? I've been using functions
> > for everything but calling them procedures in my head (even though
> > most of them do return a value or result set).
> 
> A procedure can commit or rollback a transaction (and then start
> a new one).
> 
>   regards, tom lane

thanks.

cheers,
raf

Re: Need help with clarification on stored procedure support in PostGreSQL database

[Tom Lane]

raf  writes:
> Is there any important different between a stored procedure and
> a stored function that returns void? I've been using functions
> for everything but calling them procedures in my head (even though
> most of them do return a value or result set).

A procedure can commit or rollback a transaction (and then start
a new one).

regards, tom lane

Re: Log files polluted with permission denied error messages after every 10 seconds

[Andrus]

Yeah, I've wondered for some time if the apparent EACCES code is
actually being mapped from something other than ERROR_SHARING_VIOLATION
in (some of?) these reports.  The hard part is to hold onto the
Windows error code alongside errno without massively invasive changes.


Permission denied error occur in  Windows. Debian logs doesnt contain it.

In Windows also pg_database_size('mydb')  and similar ones fail 
frequently with permission denied error.


I got information about r1soft idera backup client used. It runs only 
once per night for 16 minutes and compares disk blocks to find changed 
blocks to backup. So it should not cause permission denied errors after 
every 10 seconds.


Andrus.

Re: Need help with clarification on stored procedure support in PostGreSQL database

[raf]

On Sun, Mar 07, 2021 at 11:01:07AM -0800, Adrian Klaver 
 wrote:

> On 3/7/21 10:48 AM, Bysani, Ram wrote:
> > PostgreSQL version: 11.11
> > 
> > Operating system:   Linux 64 bit
> > 
> > Description:
> > 
> > Greetings
> > 
> > We have found posts on several forums that Stored Procedures are not
> > supported in PostGreSQL databasel.
> > 
> > We looked at the documentation but it is not clear when Stored Procedure
> > support was added. We are aware that Functions are supported.
> > 
> > Please provide details / clarify if Stored Procedures are available in
> > versions 11.x, 12.x, 13.x Please also confirm when it was actually
> > added.
> > 
> > https://www.postgresql.org/docs/ 
> > 
> > Thanks for your time
> 
> Go to page David Johnson posted:
> 
> https://www.postgresql.org/docs/current/sql-createprocedure.html
> 
> and look at 'Supported Versions:' at top of page.
> 
> > Regards
> > Ram Bysani
> 
> -- 
> Adrian Klaver
> adrian.kla...@aklaver.com

Is there any important different between a stored procedure and
a stored function that returns void? I've been using functions
for everything but calling them procedures in my head (even though
most of them do return a value or result set).

cheers,
raf

Re: Log files polluted with permission denied error messages after every 10 seconds

[Andrus]

Another thought: if it's not a sharing violation, I wonder if we
should consider dumping more raw Windows error information in the
messages we log, because, if I recall correctly, we're converting many
Windows error codes into few Unix-style error numbers and thereby
throwing away valuable clues.  It makes it a bit more confusing when
trying to ask a Windows expert what might be happening.


Knowing process name holding file and other detailed information would 
be very useful.


In other Win 2019 server/Postgres 13  excluding Postgres drive from 
fprot scan seems to decrease those errors.


However they still occur even if no fprot is active.

There are also regular stat errors in windows servers is size queries  like

ERROR: could not stat file "base/45010/172654232": Permission denied;

in query

SELECT pg_size_pretty(pg_database_size('eeva')::bigint)::char(10) as size

and

ERROR: could not stat file "base/45010/172654232": Permission denied;

in query

    SELECT
pg_catalog.pg_size_pretty(SUM(
        CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
        THEN pg_catalog.pg_database_size(d.datname)
        ELSE 0
        END)::bigint) ::char(50) AS SIZE
    FROM pg_catalog.pg_database d

and

ERROR: could not stat file "base/45010/172654232": Permission 
denied;Error while executing the query



    SELECT d.datname::char(15),
     pg_catalog.pg_get_userbyid(d.datdba)::char(18) AS Owner,
        CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
        THEN 
pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname)::bigint)

        ELSE 'No Access'
        END::char(50) AS SIZE
    FROM pg_catalog.pg_database d
        ORDER BY
        CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
        THEN pg_catalog.pg_database_size(d.datname)
        ELSE NULL
        END DESC -- nulls first

Andrus.

Re: Log files polluted with permission denied error messages after every 10 seconds

[Tom Lane]

Thomas Munro  writes:
> Another thought: if it's not a sharing violation, I wonder if we
> should consider dumping more raw Windows error information in the
> messages we log, because, if I recall correctly, we're converting many
> Windows error codes into few Unix-style error numbers and thereby
> throwing away valuable clues.  It makes it a bit more confusing when
> trying to ask a Windows expert what might be happening.

Yeah, I've wondered for some time if the apparent EACCES code is
actually being mapped from something other than ERROR_SHARING_VIOLATION
in (some of?) these reports.  The hard part is to hold onto the
Windows error code alongside errno without massively invasive changes.

regards, tom lane

Re: Log files polluted with permission denied error messages after every 10 seconds

[Thomas Munro]

On Sat, Mar 6, 2021 at 2:36 PM Michael Paquier  wrote:
> On Fri, Mar 05, 2021 at 07:36:37PM +0200, Andrus wrote:
> > Then turned real-time protection off:
> >
> > Problem persists. New entry is written after every 10 seconds.
>
> On which files are those complaints?  It seems to me that you may have
> more going on in this system that interacts with your data folder than
> you think.

Suggestion received off-list from my colleague Bevan Arps, when I was
complaining about this general variety of problem: maybe we should
look into using Windows' RestartManager[1][2] API to find out which
processes (at least the pids, maybe also names) currently have a file
open?  Then, if it is indeed a sharing violation that's causing the
problem, we might at least be able to log message that says who's
blocking us once we reach that dreaded retry loop.  There are other
ways to get that information too, I believe, no idea which API would
be best, but this one looks to be the best documented.  I'm unlikely
to work on this myself as a card carrying Unix hacker, so I'm just
passing on this insight in case it's useful...

Another thought: if it's not a sharing violation, I wonder if we
should consider dumping more raw Windows error information in the
messages we log, because, if I recall correctly, we're converting many
Windows error codes into few Unix-style error numbers and thereby
throwing away valuable clues.  It makes it a bit more confusing when
trying to ask a Windows expert what might be happening.

[1] https://docs.microsoft.com/en-us/windows/win32/rstmgr/restart-manager-portal
[2] https://devblogs.microsoft.com/oldnewthing/20120217-00/?p=8283

Re: Stored function RETURNS table, but in some cases columns are missing - should I set them to NULL?

[Tom Lane]

Alexander Farber  writes:
> However there are cases, when I only have the out_gid value, I do not want
> to return any other values.
> My question is: do I have to set the other OUT params explicitly to NULL?

plpgsql initializes them to null by default, I believe, just like ordinary
local variables.

> org.postgresql.util.PSQLException: ERROR: column "out_uid" does not exist|

This isn't related to what your function does internally.

I think the issue is you renamed out_uid to uid in your SELECT:

>String SQL_JOIN_GAME   =
>"SELECT " +
>"out_uidAS uid," +

regards, tom lane

Re: Question about when PostgreSQL 11.0 was released

[Osvaldo Kussama]

https://www.postgresql.org/docs/release/

2021-03-08 15:33 GMT-03:00, Michael Lewis :
> I can't find a page on the website right now that specifies it, but the
> feature freeze is around April each year then general availability in
> October. Minor releases are scheduled quarterly I believe. Major versions
> are EOL'd after 5 or so years.
>

Re: Stored function RETURNS table, but in some cases columns are missing - should I set them to NULL?

[Pavel Stehule]

Hi

po 8. 3. 2021 v 19:20 odesílatel Alexander Farber <
alexander.far...@gmail.com> napsal:

> Good evening,
>
> in PostgreSQL 13.2 I have a custom stored function:
>
> CREATE OR REPLACE FUNCTION words_join_new_game(
> in_uid   integer,
> in_bid   integer
> ) RETURNS table (
> -- the player to be notified (sometimes there is no such
> user)
> out_uidinteger,
> -- the id of the created game
> out_gidinteger,
> out_fcmtext,
> out_apns   text,
> out_admtext,
> out_hmstext,
> -- the most recently used social network and the user id
> there
> out_social integer,
> out_sidtext,
> -- the push notification text: the opponent has joined
> out_body   text
> ) AS
> $func$
> 
> $func$ LANGUAGE plpgsql;
>
> However there are cases, when I only have the out_gid value, I do not want
> to return any other values.
>

This is same like

CREATE OR REPLACE FUNCTION words_join_new_game(IN in_uid int, IN in_bid
integer, OUT out_uid int, OUT )


> My question is: do I have to set the other OUT params explicitly to NULL?
>
> For example here:
>
> -- case 1
> SELECT gid
> INTO   out_gid
> FROM   words_games
> WHERE  finished IS NULL
> ANDbid = in_bid
> AND(
> (player1 = in_uid AND played1 IS NULL) OR
> (player2 = in_uid AND played2 IS NULL)
> ) LIMIT 1;
>
> IF out_gid IS NOT NULL THEN
> -- should I set all the other OUT params to NULL here?
> <---
> RETURN;
> END IF;
>
> I was expecting to check for out_uid, if it is a positive number in my
> Java code with:
>
> String SQL_JOIN_GAME   =
> "SELECT " +
> "out_uidAS uid," +
> // the id of the new game is never NULL, but the other
> columns can be NULL
> "out_gidAS gid," +
> "out_fcmAS fcm," +
> "out_apns   AS apns,   " +
> "out_admAS adm," +
> "out_hmsAS hms," +
> "out_social AS social, " +
> "out_sidAS sid," +
> "out_body   AS body" +
> "FROM words_join_new_game(?::int, ?::int)";
>
> int gid = 0;
> try (Connection db = DriverManager.getConnection(mDatabaseUrl);
> PreparedStatement st = db.prepareStatement(SQL_JOIN_GAME))
> {
> st.setInt(1, mUid);
> st.setInt(2, bid);
> ResultSet rs = st.executeQuery();
> if (rs.next()) {
> // get the id of the new game
> gid = rs.getInt(KEY_GID);
> // get the id of the opponent
> int uid = rs.getInt(KEY_UID);
> // send notification to the other player
> if (uid > 0) {
> Notification n = new Notification(
> uid,
> gid,
> rs.getString(COLUMN_FCM),
> rs.getString(COLUMN_APNS),
> rs.getString(COLUMN_ADM),
> rs.getString(COLUMN_HMS),
> rs.getInt(COLUMN_SOCIAL),
> rs.getString(COLUMN_SID),
> rs.getString(COLUMN_BODY)
> );
> mServlet.sendNotification(n);
> }
> }
> }
>
> but I am getting the error:
>
> org.postgresql.util.PSQLException: ERROR: column "out_uid" does not
> exist|  Position: 8
> at
> org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2553)
> at
> org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2285)
> at
> org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:323)
> at
> org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:481)
> at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:401)
> at
> org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:164)
> at
> org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:114)
> at de.afarber.WordsListener.handleNewGame(WordsListener.java:216)
> at de.afarber.WordsListener.onWebSocketText(WordsListener.java:101)
>

are you sure so you have not more functions with the same name?

Regards

Pavel


>
> Thank you
> Alex
>
>

Re: updating PGDG 12 devel on centos7 requires llvm5.0

[Geoff Winkless]

On Mon, 8 Mar 2021 at 16:15, I wrote:
> Tried running yum update on my centos7 box. Get the following:
>
> Error: Package: postgresql12-devel-12.5-1PGDG.rhel7.x86_64 (postgresql_12)
>Requires: llvm5.0-devel >= 5.0

> I tried adding the EPEL repo just in case that was the issue but it
> hasn't made any difference.

In case anyone else hits the same issue, I had a dead network-local
cache of the epel repo in yum.repos.d that, even though it was marked
as disabled, was blocking the live repo that I added later.

Geoff

Re: Question about when PostgreSQL 11.0 was released

[Michael Lewis]

I can't find a page on the website right now that specifies it, but the
feature freeze is around April each year then general availability in
October. Minor releases are scheduled quarterly I believe. Major versions
are EOL'd after 5 or so years.

Re: Question about when PostgreSQL 11.0 was released

[Joe Conway]

On 3/8/21 1:22 PM, Bysani, Ram wrote:
> I am not finding the release cadence for the PostgreSQL databases. Please let 
> me
> know how I can find the base and dot version release dates for:
> 
> PostgreSQL 11
> PostgreSQL 12
> PostgreSQL 13


I'm not sure a consolidated list of the minor release dates exists, but the
major release dates can be seen in the table here:

https://www.postgresql.org/support/versioning/

You can pull the minor release dates from the release notes yourself -- for
example from this link for pg11:

https://www.postgresql.org/docs/11/release.html

go to this for pg 11.11

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

and you can see the release date was 2021-02-11

HTH,

Joe

-- 
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development

Re: Question about when PostgreSQL 11.0 was released

[Thomas Kellerer]

Bysani, Ram schrieb am 08.03.2021 um 19:22:

Hello community:

I am not finding the release cadence for the PostgreSQL databases. Please let 
me know how I can find the base and dot version release dates for:

PostgreSQL 11

PostgreSQL 12

PostgreSQL 13


Check the release notes for the respective .0 versions:

* https://www.postgresql.org/docs/13/release-13.html
* https://www.postgresql.org/docs/12/release-12.html
https://www.postgresql.org/docs/11/release-11.html

or from the combined release notes at: https://www.postgresql.org/docs/release/

* https://www.postgresql.org/docs/release/11.0/
* https://www.postgresql.org/docs/release/12.0/
* https://www.postgresql.org/docs/release/13.0/

Question about when PostgreSQL 11.0 was released

[Bysani, Ram]

Hello community:


I am not finding the release cadence for the PostgreSQL databases. Please let 
me know how I can find the base and dot version release dates for:


PostgreSQL 11
PostgreSQL 12
PostgreSQL 13


Thanks
Ram

Stored function RETURNS table, but in some cases columns are missing - should I set them to NULL?

[Alexander Farber]

Good evening,

in PostgreSQL 13.2 I have a custom stored function:

CREATE OR REPLACE FUNCTION words_join_new_game(
in_uid   integer,
in_bid   integer
) RETURNS table (
-- the player to be notified (sometimes there is no such
user)
out_uidinteger,
-- the id of the created game
out_gidinteger,
out_fcmtext,
out_apns   text,
out_admtext,
out_hmstext,
-- the most recently used social network and the user id
there
out_social integer,
out_sidtext,
-- the push notification text: the opponent has joined
out_body   text
) AS
$func$

$func$ LANGUAGE plpgsql;

However there are cases, when I only have the out_gid value, I do not want
to return any other values.

My question is: do I have to set the other OUT params explicitly to NULL?

For example here:

-- case 1
SELECT gid
INTO   out_gid
FROM   words_games
WHERE  finished IS NULL
ANDbid = in_bid
AND(
(player1 = in_uid AND played1 IS NULL) OR
(player2 = in_uid AND played2 IS NULL)
) LIMIT 1;

IF out_gid IS NOT NULL THEN
-- should I set all the other OUT params to NULL here?
<---
RETURN;
END IF;

I was expecting to check for out_uid, if it is a positive number in my Java
code with:

String SQL_JOIN_GAME   =
"SELECT " +
"out_uidAS uid," +
// the id of the new game is never NULL, but the other
columns can be NULL
"out_gidAS gid," +
"out_fcmAS fcm," +
"out_apns   AS apns,   " +
"out_admAS adm," +
"out_hmsAS hms," +
"out_social AS social, " +
"out_sidAS sid," +
"out_body   AS body" +
"FROM words_join_new_game(?::int, ?::int)";

int gid = 0;
try (Connection db = DriverManager.getConnection(mDatabaseUrl);
PreparedStatement st = db.prepareStatement(SQL_JOIN_GAME)) {
st.setInt(1, mUid);
st.setInt(2, bid);
ResultSet rs = st.executeQuery();
if (rs.next()) {
// get the id of the new game
gid = rs.getInt(KEY_GID);
// get the id of the opponent
int uid = rs.getInt(KEY_UID);
// send notification to the other player
if (uid > 0) {
Notification n = new Notification(
uid,
gid,
rs.getString(COLUMN_FCM),
rs.getString(COLUMN_APNS),
rs.getString(COLUMN_ADM),
rs.getString(COLUMN_HMS),
rs.getInt(COLUMN_SOCIAL),
rs.getString(COLUMN_SID),
rs.getString(COLUMN_BODY)
);
mServlet.sendNotification(n);
}
}
}

but I am getting the error:

org.postgresql.util.PSQLException: ERROR: column "out_uid" does not exist|
Position: 8
at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2553)
at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2285)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:323)
at
org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:481)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:401)
at
org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:164)
at
org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:114)
at de.afarber.WordsListener.handleNewGame(WordsListener.java:216)
at de.afarber.WordsListener.onWebSocketText(WordsListener.java:101)

Thank you
Alex

updating PGDG 12 devel on centos7 requires llvm5.0

[Geoff Winkless]

Hi

I'm clearly missing something obvious but it's passed me by what that
might be, so a pointer would be appreciated...

Tried running yum update on my centos7 box. Get the following:

Error: Package: postgresql12-devel-12.5-1PGDG.rhel7.x86_64 (postgresql_12)
   Requires: llvm5.0-devel >= 5.0

SCLO is enabled and it happily installs llvm7-toolset (and its various
subs) from there. But yum install llvm5.0-devel simply returns "No
package llvm5.0-devel available"

There's suggestions in the mailing list archives that actually this is
resolved by installing llvm7-devel package but it's not.

# yum list installed | grep llvm
llvm-toolset-7-clang.x86_64   5.0.1-4.el7 @sclo
llvm-toolset-7-clang-libs.x86_64  5.0.1-4.el7 @sclo
llvm-toolset-7-compiler-rt.x86_64 5.0.1-2.el7 @sclo
llvm-toolset-7-libomp.x86_64  5.0.1-2.el7 @sclo
llvm-toolset-7-llvm.x86_645.0.1-8.el7 @sclo
llvm-toolset-7-llvm-devel.x86_64  5.0.1-8.el7 @sclo
llvm-toolset-7-llvm-libs.x86_64   5.0.1-8.el7 @sclo
llvm-toolset-7-runtime.x86_64 5.0.1-4.el7 @sclo
llvm-toolset-7.0.x86_64   7.0.1-2.el7 @sclo
llvm-toolset-7.0-clang.x86_64 7.0.1-1.el7 @sclo
llvm-toolset-7.0-clang-libs.x86_647.0.1-1.el7 @sclo
llvm-toolset-7.0-compiler-rt.x86_64   7.0.1-3.el7 @sclo
llvm-toolset-7.0-libomp.x86_647.0.1-2.el7 @sclo
llvm-toolset-7.0-lld.x86_64   7.0.1-2.el7 @sclo
llvm-toolset-7.0-lld-libs.x86_64  7.0.1-2.el7 @sclo
llvm-toolset-7.0-lldb.x86_64  7.0.1-1.el7 @sclo
llvm-toolset-7.0-llvm.x86_64  7.0.1-4.el7 @sclo
llvm-toolset-7.0-llvm-devel.x86_647.0.1-4.el7 @sclo
llvm-toolset-7.0-llvm-libs.x86_64 7.0.1-4.el7 @sclo
llvm-toolset-7.0-python2-lit.noarch   0.7.1-1.el7 @sclo
llvm-toolset-7.0-runtime.x86_64   7.0.1-2.el7 @sclo

I tried adding the EPEL repo just in case that was the issue but it
hasn't made any difference.

Thanks

Geoff

Re: PostgreSQL container crash trouble.

[Laurenz Albe]

On Mon, 2021-03-08 at 22:30 +0900, Daisuke Ikeda wrote:
> I'm running PostgreSQL (enabled timescaledb extension) in the following 
> environment.
> And I had db pod crashed error several times irregularly.
> 
> I want to know the cause of this fail. 
> 
> Environment:
> 
>   - Base: Kubernetes(microk8s)
>   - DB: PostgreSQL 12.4 (TimescaleDB) Container
>   - DB OS: Alpine Linux
>   - microk8s host OS: CentOS 7.6, Amazon Linux 2 (Occured under some hosts)
>   - DB data location: Mounted host directory (for data persistence)
> 
> 1) PostgreSQL crash and cannot start Pod..
> 
> I noticed that the timescaledb pod was restarted repeatedly with the 
> following error.
> 
> ---
> PostgreSQL Database directory appears to contain a database; Skipping 
> initialization
> [1] LOG:  starting PostgreSQL 12.4 on x86_64-pc-linux-musl, compiled by gcc 
> (Alpine 9.3.0) 9.3.0, 64-bit
> [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
> [1] LOG:  listening on IPv6 address "::", port 5432
> [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
> [20] LOG:  database system was shut down at 2021-02-15 21:15:12 UTC
> [20] LOG:  invalid primary checkpoint record
> [20] PANIC:  could not locate a valid checkpoint record
> [1] LOG:  startup process (PID 20) was terminated by signal 6: Aborted
> [1] LOG:  aborting startup due to startup process failure
> [1] LOG:  database system is shut down
> ---
> 
> I thought it might be WAL trouble and tried to check with pg_controldata and 
> pg_waldump.

It is WAL trouble.
WAL does not contain the checkpoint from before the crash.

> I thought it might be WAL trouble and tried to check with pg_controldata and 
> pg_waldump.
>
> ---
> $ pg_controldata 
> [...]
> Latest checkpoint location:   15/8FB002C0
> Latest checkpoint's REDO location:15/8FAF3018
> Latest checkpoint's REDO WAL file:00010015008F
> [...]
>
> I checked the latest checkpoint's REDO WAL file.
> 
> ---
> $ pg_waldump -n 10 pg_wal/00010015008F 
> pg_waldump: fatal: WAL segment size must be a power of two between 1 MB and 1 
> GB, but the WAL file "00010015008F" header specifies 0 bytes
> ---
> 
> I cannot read wal data.
> This file size is 16MB (according to the wal size setting).
> But the content is all zero data. I checked this situation with "od" command.
> 
> ---
> $ od -N 40 -A d -v pg_wal/00010015008F
> 000 00 00 00 00 00 00 00 00
> 016 00 00 00 00 00 00 00 00
> 032 00 00 00 00
> 040
> ---

Looks like modifications to this file were lost.

> 2) Run pg_resetwal and successful start Pod
> 
> I ran the pg_resetwal command to repair the WAL trouble.
> And the DB pod successfully started.

Yes, but "pg_resetwal" on a crashed cluster leads to data corruption.
The best you can do now is salvage what you can.

> But, I received select query fail for some tables.
> 
> 3) failed select query
> 
> ---
> sampledb1=# select * from table1 limit 1;
> ERROR:  missing chunk number 0 for toast value 27678 in pg_toast_2619
> ---
> 
> I thought taht this error is raised for pg_statistic table (and related toast 
> table (pg_toast_2619)).
> So, I deleted the broken rows in pg_statistic table and ran ANALYZE query.
> After then, this tables is recovered.

Lucky you!

> 4) another data trouble
> 
> I had an another data trouble after pg_resetwal.
> 
> In some table, SELECT query did not return any rows.
> And INSERT query failed (no response. Waiting forever...)
> This table have only primary key index.
> I thought it might have been caused by an pkey index trouble after 
> pg_resetwal.
> I didn't know how to repair the index of the primary key, and finally I 
> dropped table and restore.

You should "pg_dumpall" the cluster and load it into a cluster
that was newly created with "initdb".

Any problems loading the data have to be resolved manually.

> I thought this wal trouble was caused by disk IO troubles. But any error was 
> not raised in OS syslog.
> I want to know any other causes.

One cause might be unreliable storage that doesn't honor fsync requests
correctly.  But given that your checkpoint location is pretty late in
the file, it seems unlikely that none of the data did make it to disk.

Somehow you lost the data for a WAL segment, and you cannot recover.

Of course, if you have a backup, you know what to do.

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

PostgreSQL container crash trouble.

[Daisuke Ikeda]

Dear all.

I'm running PostgreSQL (enabled timescaledb extension) in the following
environment.
And I had db pod crashed error several times irregularly.

I want to know the cause of this fail.

Environment:

  - Base: Kubernetes(microk8s)
  - DB: PostgreSQL 12.4 (TimescaleDB) Container
  - DB OS: Alpine Linux
  - microk8s host OS: CentOS 7.6, Amazon Linux 2 (Occured under some hosts)
  - DB data location: Mounted host directory (for data persistence)

1) PostgreSQL crash and cannot start Pod..

I noticed that the timescaledb pod was restarted repeatedly with the
following error.

---
PostgreSQL Database directory appears to contain a database; Skipping
initialization
[1] LOG:  starting PostgreSQL 12.4 on x86_64-pc-linux-musl, compiled by gcc
(Alpine 9.3.0) 9.3.0, 64-bit
[1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
[1] LOG:  listening on IPv6 address "::", port 5432
[1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
[20] LOG:  database system was shut down at 2021-02-15 21:15:12 UTC
[20] LOG:  invalid primary checkpoint record
[20] PANIC:  could not locate a valid checkpoint record
[1] LOG:  startup process (PID 20) was terminated by signal 6: Aborted
[1] LOG:  aborting startup due to startup process failure
[1] LOG:  database system is shut down
---

I thought it might be WAL trouble and tried to check with pg_controldata
and pg_waldump.

---
$ pg_controldata 
pg_control version number:1201
Catalog version number:   201909212
Database system identifier:   6909006008117780509
Database cluster state:   in production
pg_control last modified: Mon Feb 15 21:11:21 2021
Latest checkpoint location:   15/8FB002C0
Latest checkpoint's REDO location:15/8FAF3018
Latest checkpoint's REDO WAL file:00010015008F
Latest checkpoint's TimeLineID:   1
Latest checkpoint's PrevTimeLineID:   1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID:  0:33988723
Latest checkpoint's NextOID:  117637
Latest checkpoint's NextMultiXactId:  1
Latest checkpoint's NextMultiOffset:  0
Latest checkpoint's oldestXID:480
Latest checkpoint's oldestXID's DB:   1
Latest checkpoint's oldestActiveXID:  33988723
Latest checkpoint's oldestMultiXid:   1
Latest checkpoint's oldestMulti's DB: 1
Latest checkpoint's oldestCommitTsXid:0
Latest checkpoint's newestCommitTsXid:0
Time of latest checkpoint:Mon Feb 15 21:11:14 2021
Fake LSN counter for unlogged rels:   0/3E8
Minimum recovery ending location: 0/0
Min recovery ending loc's timeline:   0
Backup start location:0/0
Backup end location:  0/0
End-of-backup record required:no
wal_level setting:replica
wal_log_hints setting:off
max_connections setting:  50
max_worker_processes setting: 15
max_wal_senders setting:  10
max_prepared_xacts setting:   0
max_locks_per_xact setting:   64
track_commit_timestamp setting:   off
Maximum data alignment:   8
Database block size:  8192
Blocks per segment of large relation: 131072
WAL block size:   8192
Bytes per WAL segment:16777216
Maximum length of identifiers:64
Maximum columns in an index:  32
Maximum size of a TOAST chunk:1996
Size of a large-object chunk: 2048
Date/time type storage:   64-bit integers
Float4 argument passing:  by value
Float8 argument passing:  by value
Data page checksum version:   0
Mock authentication nonce:
 871e705d9393cdb1b161d5562656e20db314779af25942fa10d840983dc4ba84
---

I checked the latest checkpoint's REDO WAL file.

---
$ pg_waldump -n 10 pg_wal/00010015008F
pg_waldump: fatal: WAL segment size must be a power of two between 1 MB and
1 GB, but the WAL file "00010015008F" header specifies 0 bytes
---

I cannot read wal data.
This file size is 16MB (according to the wal size setting).
But the content is all zero data. I checked this situation with "od"
command.

---
$ od -N 40 -A d -v pg_wal/00010015008F
000 00 00 00 00 00 00 00 00
016 00 00 00 00 00 00 00 00
032 00 00 00 00
040
---


The WAL log of the previous one could be seen with pg_waldump.
However, I could not find any log corresponding to the latest checkpoint
location (8FB002C0) listed in pg_controldata.


2) Run pg_resetwal and successful start Pod

I ran the pg_resetwal command to repair the WAL trouble.
And the DB pod successfully started.

But, I received select query fail for some tables.

3) failed select query

---
sampledb1=# select * from table1 limit 1;
ERROR:  missing chunk number 0 for toast value 27678 in pg_toast_2619
---

I thought taht this error is raised for pg_statistic table (and related