Re: Postgresql error : PANIC: could not locate a valid checkpoint record

[Mateusz Henicz]

Hi,
Have you done pg_upgrade post Postgres 13 installation?

https://www.postgresql.org/docs/13/pgupgrade.html

Cheers,
Mateusz

pt., 17 cze 2022 o 11:20 Mahendrakar, Prabhakar - Dell Team <
prabhakar.mahendr...@dellteam.com> napisał(a):

> Hello,
>
>
>
> Good Morning !
>
>
>
> We are facing *checkpoint* related issues from PostGreSQL 13.4 ( could
> not locate a valid checkpoint record) and Postgres service fails to come up.
>
>
> 
>
> *LOG:  starting PostgreSQL 13.4 on x86_64-pc-linux-gnu, compiled by gcc
> (GCC) 4.4.7 20120313 (Red Hat 4.4.7-16), 64-bit*
>
> *LOG:  listening on IPv4 address "127.0.0.1", port 9003*
>
> *LOG:  listening on IPv4 address "10.xx.xx.x..", port 9003*
>
> *LOG:  listening on Unix socket "/tmp/.s.PGSQL.9003"*
>
> *LOG:  database system was shut down at 2022-06-09 10:19:24 CEST*
>
> *LOG:  invalid primary checkpoint record*
>
> *PANIC:  could not locate a valid checkpoint record*
>
> *LOG:  startup process (PID 8773) was terminated by signal 6: Aborted*
>
> *LOG:  aborting startup due to startup process failure*
>
> *LOG:  database system is shut down*
>
>
>
> This issue is seen in both Windows and Linux OS platforms.
>
>
>
> To Brief on the Scenario: Our product say example with Version A1 uses
> Postgres 10 and in the latest version of our product (Say A2) we upgraded
> the Postgres to 13.
>
>When we try to upgrade our
> Product through InstallAnyWhere from A1 to A2, Postgres service fails with
> above mentioned error.
>
>
>
>  Could you please suggest the probable cause of the issue. Let us know if
> you require any more information.
>
>
>
> Thanks,
>
> Prabhakar
>
>
>
> Internal Use - Confidential
>

Re: Postgresql error : PANIC: could not locate a valid checkpoint record

[Mateusz Henicz]

Assuming you have shut down your postgres properly before upgrading, it
should be safe for you to run pg_resetwal.
https://www.postgresql.org/docs/current/app-pgresetwal.html
It should help in this case.

pt., 17 cze 2022 o 12:03 Mahendrakar, Prabhakar - Dell Team <
prabhakar.mahendr...@dellteam.com> napisał(a):

> Yes, We are using the pg_upgrade utility of Postgres.
>
>
>
> *From:* Mateusz Henicz 
> *Sent:* Friday, June 17, 2022 3:31 PM
> *To:* Mahendrakar, Prabhakar - Dell Team
> *Cc:* pgsql-general@lists.postgresql.org
> *Subject:* Re: Postgresql error : PANIC: could not locate a valid
> checkpoint record
>
>
>
> [EXTERNAL EMAIL]
>
> Hi,
>
> Have you done pg_upgrade post Postgres 13 installation?
>
>
> https://www.postgresql.org/docs/13/pgupgrade.html [postgresql.org]
> <https://urldefense.com/v3/__https:/www.postgresql.org/docs/13/pgupgrade.html__;!!LpKI!idvJ795EwLEkEftyMMQc6j4AvHBN-AWOsHhukZYHV0VBPR8PRtDCRs_JDRGs5Nz34Vs4VK05szBLxdJ13Wzjztu6q67YUhUY$>
>
>
>
> Cheers,
> Mateusz
>
>
>
> pt., 17 cze 2022 o 11:20 Mahendrakar, Prabhakar - Dell Team <
> prabhakar.mahendr...@dellteam.com> napisał(a):
>
> Hello,
>
>
>
> Good Morning !
>
>
>
> We are facing *checkpoint* related issues from PostGreSQL 13.4 ( could
> not locate a valid checkpoint record) and Postgres service fails to come up.
>
>
> 
>
> *LOG:  starting PostgreSQL 13.4 on x86_64-pc-linux-gnu, compiled by gcc
> (GCC) 4.4.7 20120313 (Red Hat 4.4.7-16), 64-bit*
>
> *LOG:  listening on IPv4 address "127.0.0.1", port 9003*
>
> *LOG:  listening on IPv4 address "10.xx.xx.x..", port 9003*
>
> *LOG:  listening on Unix socket "/tmp/.s.PGSQL.9003"*
>
> *LOG:  database system was shut down at 2022-06-09 10:19:24 CEST*
>
> *LOG:  invalid primary checkpoint record*
>
> *PANIC:  could not locate a valid checkpoint record*
>
> *LOG:  startup process (PID 8773) was terminated by signal 6: Aborted*
>
> *LOG:  aborting startup due to startup process failure*
>
> *LOG:  database system is shut down*
>
>
>
> This issue is seen in both Windows and Linux OS platforms.
>
>
>
> To Brief on the Scenario: Our product say example with Version A1 uses
> Postgres 10 and in the latest version of our product (Say A2) we upgraded
> the Postgres to 13.
>
>When we try to upgrade our
> Product through InstallAnyWhere from A1 to A2, Postgres service fails with
> above mentioned error.
>
>
>
>  Could you please suggest the probable cause of the issue. Let us know if
> you require any more information.
>
>
>
> Thanks,
>
> Prabhakar
>
>
>
>
>
> Internal Use - Confidential
>
>
>
> Internal Use - Confidential
>
>

Re: Postgresql error : PANIC: could not locate a valid checkpoint record

[Mateusz Henicz]

Could you also answer on questions asked by Laurenz Albe a few emails back?
That could shed some light into your issue. We would know if the upgrade
was performed properly and possibly point where the issue can be.
Also, are you able to reproduce the issue? I.e. try restoring the database
to state before upgrade, try to upgrade again. Does this corruption happen
again?
About pg_resetwal, you are right that it should be done as a last resort.
On the other hand, if your production database does not start after upgrade
and you do not have any way to rollback your changes it might be it.

Cheers,
Mateusz

wt., 21 cze 2022 o 14:02 Mahendrakar, Prabhakar - Dell Team <
prabhakar.mahendr...@dellteam.com> napisał(a):

> Could you please provide some insights in the below query. We are in need
> of this as it this issue is seen at some of our customer’s site.
>
>
>
> Thanks,
>
> Prabhakar
>
>
>
>
>
> Internal Use - Confidential
>
> *From:* Mahendrakar, Prabhakar - Dell Team
> *Sent:* Monday, June 20, 2022 4:22 PM
> *To:* Mateusz Henicz
> *Cc:* pgsql-general@lists.postgresql.org
> *Subject:* RE: Postgresql error : PANIC: could not locate a valid
> checkpoint record
>
>
>
> Thanks for the response.
>
> Yes, we have taken care of proper shut down of Postgres before initiating
> the Upgrade.
>
> pg_resetwal – I have read that using pg_resetwal may cause the Database
> more inconsistent and should be used only as a last resort.
>
>
>
> Also this problem ( *checkpoint* related issue -could not locate a valid
> checkpoint record ) is not happening frequently. This issue is seen with
> the large size of Data base.
>
> Please let me know if you require any more information.
>
>
>
> Thanks,
>
> Prabhakar
>
>
>
> *From:* Mateusz Henicz 
> *Sent:* Friday, June 17, 2022 3:39 PM
> *To:* Mahendrakar, Prabhakar - Dell Team
> *Cc:* pgsql-general@lists.postgresql.org
> *Subject:* Re: Postgresql error : PANIC: could not locate a valid
> checkpoint record
>
>
>
> [EXTERNAL EMAIL]
>
> Assuming you have shut down your postgres properly before upgrading, it
> should be safe for you to run pg_resetwal.
>
> https://www.postgresql.org/docs/current/app-pgresetwal.html
> [postgresql.org]
> <https://urldefense.com/v3/__https:/www.postgresql.org/docs/current/app-pgresetwal.html__;!!LpKI!nLsPoYLxz9_0wbkgjbQGXorp1s6PcSTlir-of_nvRmQZazd2S0Rnqe7iZ8l6oW7aarWf8v4_p1LdgHThZk0YSPfaD4cz4g-K$>
>
>
> It should help in this case.
>
>
>
> pt., 17 cze 2022 o 12:03 Mahendrakar, Prabhakar - Dell Team <
> prabhakar.mahendr...@dellteam.com> napisał(a):
>
> Yes, We are using the pg_upgrade utility of Postgres.
>
>
>
> *From:* Mateusz Henicz 
> *Sent:* Friday, June 17, 2022 3:31 PM
> *To:* Mahendrakar, Prabhakar - Dell Team
> *Cc:* pgsql-general@lists.postgresql.org
> *Subject:* Re: Postgresql error : PANIC: could not locate a valid
> checkpoint record
>
>
>
> [EXTERNAL EMAIL]
>
> Hi,
>
> Have you done pg_upgrade post Postgres 13 installation?
>
>
> https://www.postgresql.org/docs/13/pgupgrade.html [postgresql.org]
> <https://urldefense.com/v3/__https:/www.postgresql.org/docs/13/pgupgrade.html__;!!LpKI!idvJ795EwLEkEftyMMQc6j4AvHBN-AWOsHhukZYHV0VBPR8PRtDCRs_JDRGs5Nz34Vs4VK05szBLxdJ13Wzjztu6q67YUhUY$>
>
>
>
> Cheers,
> Mateusz
>
>
>
> pt., 17 cze 2022 o 11:20 Mahendrakar, Prabhakar - Dell Team <
> prabhakar.mahendr...@dellteam.com> napisał(a):
>
> Hello,
>
>
>
> Good Morning !
>
>
>
> We are facing *checkpoint* related issues from PostGreSQL 13.4 ( could
> not locate a valid checkpoint record) and Postgres service fails to come up.
>
>
> 
>
> *LOG:  starting PostgreSQL 13.4 on x86_64-pc-linux-gnu, compiled by gcc
> (GCC) 4.4.7 20120313 (Red Hat 4.4.7-16), 64-bit*
>
> *LOG:  listening on IPv4 address "127.0.0.1", port 9003*
>
> *LOG:  listening on IPv4 address "10.xx.xx.x..", port 9003*
>
> *LOG:  listening on Unix socket "/tmp/.s.PGSQL.9003"*
>
> *LOG:  database system was shut down at 2022-06-09 10:19:24 CEST*
>
> *LOG:  invalid primary checkpoint record*
>
> *PANIC:  could not locate a valid checkpoint record*
>
> *LOG:  startup process (PID 8773) was terminated by signal 6: Aborted*
>
> *LOG:  aborting startup due to startup process failure*
>
> *LOG:  database system is shut down*
>
>
>
> This issue is seen in both Windows and Linux OS platforms.
>
>
>
> To Brief on the Scenario: Our product say example with Version A1 uses
> Postgres 10 and in the latest version of our product (Say A2) we upgraded
> the Postgres to 13.
>
>When we try to upgrade our
> Product through InstallAnyWhere from A1 to A2, Postgres service fails with
> above mentioned error.
>
>
>
>  Could you please suggest the probable cause of the issue. Let us know if
> you require any more information.
>
>
>
> Thanks,
>
> Prabhakar
>
>
>
>
>
> Internal Use - Confidential
>
>
>
> Internal Use - Confidential
>
>
>
> Internal Use - Confidential
>
>

Re: Feature request: psql --idle

[Mateusz Henicz]

Hi,
Your first sentence is wrong. Changing pg_hba.conf file does not require
server restart. It is enough to reload the configuration using "pg_ctl
reload", "select pg_reload_conf();" in psql or just sending SIGHUP from
linux terminal to postmaster process after changing the pg_hba file.

To achieve something like this you could use simple bash script like the
one below and add it to your /etc/passwd file, like
/etc/passwd
test:x:1001:1001::/home/test:/home/test/skrypt.sh

 /home/test/skrypt.sh
#!/bin/bash
echo "Select a database"
echo "1. local"
echo "2. other_dbs"

read dbname

if [ "$dbname" == "1" ] || [ "$dbname" == "local" ]
then
  psql -h 127.0.0.1 -p 5432 -U postgres postgres
elif  [ "$dbname" == "2" ] || [ "$dbname" == " other_dbs  " ]
then
  psql -h 127.0.0.1 -p  -U postgres postgres
fi

Hope it helps.

Cheers,
Mateusz

śr., 27 lip 2022 o 14:50 Wiwwo Staff  napisał(a):

> Since changing ph_hda.conf file to give users access involves the restart
> of server, many companies I work(ed) use a bastion host, where users ssh
> to, and are allowed "somehow" use postgresql.
>
> Still, those users need some login shell.
>
> It would be great to give them psql as a login shell (in /etc/passwd).
> But doing so, will result in psql exiting with error with the usual
> $ psql
> psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed:
> No such file or directory
> Is the server running locally and accepting connections on that socket?
>
> What would help, is a --idle option, where psql does not exit, stays idle
> and waits for user to give a \conn command.
> Something similar to
> sqlplus /nolog
>
> Is anything like that feasible or is there another solution/workaround?
>
> Tnx!
>

Re: Feature request(?): Proxy User

[Mateusz Henicz]

Hi,
Did you check "SET SESSION AUTHORIZATION" or "SET ROLE" commands?
I think that is what you are looking for.

Cheers,
Mateusz

czw., 28 lip 2022 o 11:29 Wiwwo Staff  napisał(a):

> Sorry to post solutions and links about alternative DBMSs, but I miss this
> sort of "sudo" ad database user level:
> https://oracle-base.com/articles/misc/proxy-users-and-connect-through
>
> Is there any approach to achieve the same result in Postgresql?
> Anyone sharing the need of this functionality?
>
> Tnx!
>
>

Re: BUG #17567: Unable to Set Max_Connection in Postgresql which has replicas

[Mateusz Henicz]

Hey,
It is not a bug.
If you are using patroni, you should change your parameters in patroni
configuration yaml file, so the changes are reflected in your Distributed
Configuration Store. If you would look at the Patroni documentation (
https://patroni.readthedocs.io/en/latest/SETTINGS.html) you can find a
note, that parameters like max_connections are stored in DCS and applied on
all Patroni nodes overwriting settings from postgresql.conf. Also if
Postgres was bootstrapped by Patroni, postgresql.conf itself should have a
note:

# Do not edit this file manually!
# It will be overwritten by Patroni!

Hope it helps.

Cheers,
Mateusz




czw., 4 sie 2022 o 10:21 Poornima Venkatesan 
napisał(a):

>
> Hi Team,
>
> PostgreSQL in our Production environment is configured using primary and
> secondary DB servers via Patroni. We have requirement to explicitly to set
> Max_Connection as 300 overriding default value. If we set Max_Connections
> in
> postgresql.config, it is getting reset post restart of Patroni.
>
> Please advise us on the steps on how to configure and set Max_Connections
> in
> Replica DB servers.
>
> Thanks & Regards,
> Poornima V
>
>
> On Wed, Aug 3, 2022 at 4:43 PM Juan José Santamaría Flecha <
> juanjo.santama...@gmail.com> wrote:
>
>>
>> On Wed, Aug 3, 2022 at 12:48 PM PG Bug reporting form <
>> nore...@postgresql.org> wrote:
>>
>>>
>>> Please advise us on the steps on how to configure and set
>>> Max_Connections in
>>> Replica DB servers.
>>>
>>
>> This list is meant for reporting bugs only, please send questions like so
>> to pgsql-general@lists.postgresql.org.
>>
>> Regards,
>>
>> Juan José Santamaría Flecha
>>
>

Re: Monitoring-only role

[Mateusz Henicz]

Hey,
There is pg_monitor role. I can "Read/execute various monitoring views and
functions. This role is a member of pg_read_all_settings, pg_read_all_stats
 and pg_stat_scan_tables."
https://www.postgresql.org/docs/14/predefined-roles.html

Cheers,
Mateusz

wt., 6 gru 2022 o 13:37 Wiwwo Staff  napisał(a):

> Hi there!
> Is there (a way to have) a "monitoring only" role in PG?
> This role should have access to stat views only,maybe something more, but
> no access to data whatsoever.
>
> Tnx!
>

Re: configure a patroni cluster with a follower who never can become leader

[Mateusz Henicz]

Hi,

Adding a tag "nofailover: true" for nodes that can't be promoted to leaders
should do the trick.

https://patroni.readthedocs.io/en/latest/SETTINGS.html#tags

tags:
nofailover: true

Cheers,
Mateusz

pt., 9 gru 2022 o 11:03 Marco Lechner  napisał(a):

> Hi,
>
>
>
> anyone who can point me to a docu or give me a hint if and how it is
> possible to configure a patroni cluster in a way that some nodes are
> follower and can become leader if necessary but there are followers as well
> that never can become a leader?
>
>
>
> VG
>
> Marco
>
>
>
> i.A. Dr. Marco Lechner
>
> Leiter Fachgebiet RN 1 │ Head RN 1
>
>
>
> --
>
> Bundesamt für Strahlenschutz │ Federal Office for Radiation Protection
>
> Koordination Notfallschutzsysteme │ Coordination Emergency Systems │ RN 1
>
> Rosastr. 9
>
> D-79098 Freiburg
>
>
>
> Tel.: +49 30 18333-6724
>
> E-Mail: mlech...@bfs.de
>
> *www.bfs.de *
>
> *🌐* *Besuchen* * Sie unsere Website, f**olgen Sie
> uns auf **Twitter ** und *
> *abonnieren* * Sie unseren **📢*
> *Newsletter.*
>
> 🔒* Informationen zum **Datenschutz* *
> gemäß Artikel 13 DSGVO*
>
> 💚 *E-Mail drucken? Lieber die Umwelt schonen!*
>
>
>
> --
>
> Hinweis zu Anhängen die auf .p7m/.p7c/.p7s oder .asc/.asc.sig enden:
> Die .p7?- und .asc-Dateien sind ungefährliche Signaturdateien (digitale
> Unterschriften). In E-Mail-Clients mit S/MIME Konfiguration (.p7?) oder
> PGP-Erweiterung (.asc) dienen sie zur:
> - Überprüfung des Absenders
> - Überprüfung einer evtl. Veränderung des Inhalts während der Übermittlung
> über das Internet
> Die Signaturdateien können ebenso dazu verwendet werden dem Absender
> dieser Signatur eine E-Mail mit verschlüsseltem Inhalt zu senden. In
> E-Mail-Clients ohne S/MIME Konfiguration oder PGP-Erweiterung erscheinen
> die Dateien als Anhang und können ignoriert werden.
>
>
>

Re: pg_rewind and replication user

[Mateusz Henicz]

Hey,
If you would look into docs
https://www.postgresql.org/docs/current/app-pgrewind.html on the "Notes"
section you will find a list of permissions that user needs to have to be
able to run pg_rewind.

Cheers,
Mateusz

śr., 1 lut 2023, 15:09 użytkownik Wiwwo Staff  napisał:

> Hi!
> Provided my replication user created with
> CREATE USER repl_user REPLICATION LOGIN ENCRYPTED PASSWORD'';
>
> If I run pg_rewing referring to this user
> postgres@host1:~: pg_rewind -D $PGDATA --source-server="host=nre_primary
> port=5432 user=repl_user passfile='/var/lib/postgresql/.pgpass'
> dbname=postgres" -P -R
>
> I get
> pg_rewind: connected to server
> pg_rewind: error: could not fetch remote file "global/pg_control": ERROR:
>  permission denied for function pg_read_binary_file
>
> My question/doubt is: i can need to run pg_rewind referring to a superuser
> only? Or i am "just" forgetting some grant to the replication user?
>
> Thanks!
>
>

Re: Logical replication restarts

[Mateusz Henicz]

Hi,
There should be another line in the log for why the logical replication
worker has stopped in the first place. Something like Primary Key or Unique
Constraint violation, timeout on WAL sender or receiver process, etc.
Unless you can provide that information I doubt someone will be able to
help here.

Check logs on your both publisher and subscriber, with this information you
should be able to find the solution.

Cheers,
Mateusz

czw., 6 lip 2023 o 14:34 Paula Berenguel 
napisał(a):

> Hi
> I am using logical replication to move an on premises database PG11 to a
> cloud based PG11 and experiencing constant restart of the replication.
>
> The replication does it again. The validation_entry started again:
>
> 2023-07-06 01:49:46.968 UTC-64a61dba.1ad8LOG: 0: logical
> replication table synchronization worker for subscription "sub", table
> "ZZZYYY" has started
>
> 2023-07-04 21:10:36.478 UTC-64a48b93.961LOG: 0: logical
> replication table synchronization worker for subscription "sub",
> table " ZZZYYY" has started
>
>
> Can someone help me to find a fix for this?
>
>
> Thanks
>
> Paula
>

Re: Reset Postgresql users password

[Mateusz Henicz]

You can also just write an sql and execute it, like:

select 'alter user '|| usename ||' with password ''newpassword'';'  from
pg_user;
\gexec

Cheers,
Mateusz

śr., 12 lip 2023 o 21:35 Gianni Ceccarelli 
napisał(a):

> On 2023-07-12 Johnathan Tiamoh  wrote:
> > I wish to find out if there is a way to reset all users in Postgresql
> > password to the same password at once.
>
> I guess you could update the `pg_catalog.pg_authid` table, see
> https://www.postgresql.org/docs/15/catalog-pg-authid.html
>
> --
> Dakkar - 
> GPG public key fingerprint = A071 E618 DD2C 5901 9574
>  6FE2 40EA 9883 7519 3F88
> key id = 0x75193F88
>
>
>
>

Re: Creating a TABLESPACE

[Mateusz Henicz]

Hey,
You are using "WITH tablespace_option" wrong.

Try it like this:

postgres=# CREATE TABLESPACE jme_tablespace OWNER jamiil LOCATION
'/database' WITH
(seq_page_cost=1.0,random_page_cost=4.0,effective_io_concurrency=1);
CREATE TABLESPACE

Cheers,
Mateusz

śr., 16 sie 2023 o 12:10 Amn Ojee Uw  napisał(a):

> Hello children!
>
> I am learning how to create a tablespace, and AFAIK this are the steps to
> follow.
>
> First step is to create the directory where the database files will be
> created, for that I am using the root directory '/database'. This directory
> is be owned by the postgres group.
>
> Example :
> sudo mkdir /database
> sudo chown postgres:postgres /database
>
> I read the following documentation
> 1.) TABLESPACE
> 
>
> 2.) seq_page_cost
> 
>
> 3.) effective_io_concurrency
> 
>
> 4.) maintenance_io_concurrency
> 
>
> 5.) Tablespaces
> 
>
> x.) effective_io_concurrency
> 
>
> I then login PostgreSQL as so : sudo -u postgres psql.
>
> Note : *jamiil* is an actual account, it does exist!
>
> I proceed to create the 'TABLESPACE' using the default values in PostgreSQL
>
> CREATE TABLESPACE jme_tablespace OWNER jamiil LOCATION '/database' WITH
> tablespace_option = seq_page_cost(1.0), random_page_cost (4.0),
> effective_io_concurrency (1), maintenance_io_concurrency (10);
>
> But then I get this error message:
> *ERROR:  syntax error at or near "tablespace_option"*
> *LINE 1: ...tablespace OWNER jamiil LOCATION '/database' WITH
> tablespace...*
>
> What did I go wrong?
>
> This exercise is not imperative, but as a student of PostgreSQL I'd like
> to try all there is to learn about PostgreSQL.
>
> Thanks in advance for your time and interest.
>

Re: missing client_hostname

[Mateusz Henicz]

Did you reconnect your replica after enabling log_hostname? If not, then do
it and check again.

śr., 1 lis 2023, 19:03 użytkownik Atul Kumar 
napisał:

> I have already enabled log_hostname, still  *client_hostname is not
> showing up.*
>
> Do you think that just because I use ip instead of hostname while using
> pg_basebackup could be the reason for it ?
>
>
>
>
> Regards,
> Atul
>
> On Wed, Nov 1, 2023 at 11:23 PM Christoph Moench-Tegeder <
> c...@burggraben.net> wrote:
>
>> ## Atul Kumar (akumar14...@gmail.com):
>>
>> > It was successfully configured but when I query pg_stat_replication I
>> don't
>> > get the hostname in output:
>>
>> I Recommend The Fine Manual:
>>
>> https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-REPLICATION-VIEW
>> "... and only when log_hostname is enabled".
>>
>> Regards,
>> Christoph
>>
>> --
>> Spare Space
>>
>

Re: archive command doesnt work

[Mateusz Henicz]

czw., 8 lut 2024 o 21:10 Laurenz Albe  napisał(a):

> On Thu, 2024-02-08 at 09:23 +0100, Alpaslan AKDAĞ wrote:
> > Thank you for your answer.
> >
> > I have checked the pg_is_in_recovery() and here are the results
> > from primary and hot stand by server.
> >
> > Primary (old standby ):
> > select pg_is_in_recovery();
> >  pg_is_in_recovery
> > ---
> >  f
> > (1 row)
> >
> > hot standby(old primary):
> > select pg_is_in_recovery();
> >  pg_is_in_recovery
> > ---
> >  t
> > (1 row)
> > and there is also standby.signal file in standby server.
> > So it seems that there is nothing wrong.
> >
> > recovery_min_apply_delay = '2d' are set in warm standby servers.
> > Before the switchover we had the same settings but we did not have this
> problem.
>
> It is becoming increasingly obvious that you never actually performed
> a switchover: it seems that you didn't promote the standby.
>
> Either use
>
>   pg_ctl promote -D /path/to/datadir
>
> on the command line or
>
>   SELECT pg_promote();
>
> in SQL.
>
> Yours,
> Laurenz Albe
>
>
>
>From what you sent:


*"After switchover since 01.02.2024 hot standby server does not archive wal
files. I couldn't find the problem."*
Hot standby normally does not archive WAL files. Only the primary server
does that unless you have archive_mode set to "always" but your
configuration shows that you have it "on" only.


*"related postgresql.conf lines:*
*archive_mode = on "*

Cheers,
Mateusz

Re: archive command doesnt work

[Mateusz Henicz]

Hey,
Yes, it is.
https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-ARCHIVE-MODE

czw., 8 lut 2024 o 21:35 Alpaslan AKDAĞ 
napisał(a):

> Hello Mateusz
>
> Thank you for your answer.
>
> If it is like that, there is not any problem. I tried to find an
> explanation that hot standby archive wal files or not but I couldn't.
> Is this information  from the Documentation?
>
> Best Regards,
> Alpaslan
>
> On Thu, Feb 8, 2024 at 9:24 PM Mateusz Henicz 
> wrote:
>
>>
>> czw., 8 lut 2024 o 21:10 Laurenz Albe 
>> napisał(a):
>>
>>> On Thu, 2024-02-08 at 09:23 +0100, Alpaslan AKDAĞ wrote:
>>> > Thank you for your answer.
>>> >
>>> > I have checked the pg_is_in_recovery() and here are the results
>>> > from primary and hot stand by server.
>>> >
>>> > Primary (old standby ):
>>> > select pg_is_in_recovery();
>>> >  pg_is_in_recovery
>>> > ---
>>> >  f
>>> > (1 row)
>>> >
>>> > hot standby(old primary):
>>> > select pg_is_in_recovery();
>>> >  pg_is_in_recovery
>>> > ---
>>> >  t
>>> > (1 row)
>>> > and there is also standby.signal file in standby server.
>>> > So it seems that there is nothing wrong.
>>> >
>>> > recovery_min_apply_delay = '2d' are set in warm standby servers.
>>> > Before the switchover we had the same settings but we did not have
>>> this problem.
>>>
>>> It is becoming increasingly obvious that you never actually performed
>>> a switchover: it seems that you didn't promote the standby.
>>>
>>> Either use
>>>
>>>   pg_ctl promote -D /path/to/datadir
>>>
>>> on the command line or
>>>
>>>   SELECT pg_promote();
>>>
>>> in SQL.
>>>
>>> Yours,
>>> Laurenz Albe
>>>
>>>
>>>
>> From what you sent:
>>
>>
>> *"After switchover since 01.02.2024 hot standby server does not archive
>> wal files. I couldn't find the problem."*
>> Hot standby normally does not archive WAL files. Only the primary server
>> does that unless you have archive_mode set to "always" but your
>> configuration shows that you have it "on" only.
>>
>>
>> *"related postgresql.conf lines:*
>> *archive_mode = on "*
>>
>> Cheers,
>> Mateusz
>>
>

Re: Create a standby server

[Mateusz Henicz]

Hey,
Check your parameter primary_conninfo on any standby server, you should
find here information about the user used for replication and its password
or path to .pgpass file, where the password is stored. If there is no
password or .pgpass file defined, then you do not need any password most
likely, and you are likely using the "trust" authentication method in your
pg_hba.conf for replication.

To create a replica pretty much all you have to do is to add your new
standby server to pg_hba.conf, so you are allowed to connect and run on
your new standby:
pg_basebackup -h  -U  -R -D /data_pg
-X stream
and then start it using
pg_ctl -D /data_pg start

There can be some parameters that need to be adjusted, like
listen_addresses for example, unless you are using "*" for it. And maybe
max_wal_senders/max_replication_slots can be too low and you may have to
increase it, but if there is any problem and you will try to start your new
replica it will just fail and you will get information about what was wrong
to your logfile, so it is easy to find.

By adding -R to pg_basebackup you will get your replication configuration
generated automatically to postgresql.auto.conf and -X will stream all WAL
files generated during pg_basebackup execution to your new replica server.
If you prefer to use replication slots you may also add -C -S 
to get a replication slot created automatically by pg_basebackup.

Good luck!

Cheers,
Mateusz

pon., 11 mar 2024 o 12:51 normandavis1990 
napisał(a):

> Hello,
> I have a master and tow standby servers. I want to create another
> one. These servers are made by someone else and I am a newbie in PostgreSQL.
> I found the following two tutorials:
>
> https://linuxconfig.org/how-to-create-a-hot-standby-with-postgresql
>
>
> https://github.com/GoogleCloudPlatform/community/blob/master/archived/setting-up-postgres-hot-standby.md
>
> A) Which on is better and easier?
>
> B) In these articles, to create a Standby server, a user is created in the
> database. Because there are already two Standby servers, this user is
> probably created. How can I find it? Can I use that user to build a third
> server?
>
>
> Cheers.
>
>
>

Re: PgBackRest PTR recovery: After table drop to get dropped state

[Mateusz Henicz]

When you are performing PITR you need to configure a timestamp before your
last committed transaction. In your case you provided timestamp after your
last commit.

When postgtes is restoring until a specified point, it restores a
transaction from WAL, and checking if next transaction is before or after
said timestamp. If it is before it will replay it and check next
transaction. Until next transaction is after configured timestamp.
If there is no transaction after your current timestamp in current WAL,
postgres will try to restore next WAL from archive. And since there is no
next WAL, and your timestamp is past latest committed transaction, it is
unable to continue, because it does not know if there should be any other
transaction replayed or not.

Just perform some other actions after you note down the timestamp after
drop table. Create another one, insert some data, do whatever to have
another transaction in WALs.

Cheers,
Mateusz

czw., 1 sie 2024 o 12:23 KK CHN  napisał(a):

> The logs are here.
>
> https://pastecode.io/s/s5dp8ur1
>
>
>
> On Thu, Aug 1, 2024 at 3:30 PM Kashif Zeeshan 
> wrote:
>
>> Hi
>>
>> On Thu, Aug 1, 2024 at 2:54 PM KK CHN  wrote:
>>
>>> List,
>>>
>>> *Not working (start EPAS server always fails):*
>>>
>>> 1. Testing PTR using  PgBackRest(2.52.1)  on RHEL9  EPAS-16, and RHEL9 (
>>> Repo   Server)
>>>
>>>   When I do a PTR
>>>
>>> 1.  After doing a table drop and then
>>> 2. Noting down the time stamp and then
>>> 3. Taking an incremental backup in hope that If I do a restore from this
>>> incr Backup, that won't  contain the  dropped table.
>>> 4. Correct me  if I am  conceptually wrong here.
>>> 5.  I am *never *successful in restoring the EPAS server in this
>>> scenario.
>>>
>>>
>>> *I know the following will work for me, w*hy not the above one if I
>>> really want that state of cluster also  ?
>>>
>>> *This is Working. *
>>>  1. Create table
>>> 2. Noting down the timestamp
>>> 3.  Taking incremental backup on RepoServer.
>>> 4. drop the created table .
>>> 5. Then stop the EPAS server and do a  PTR, by the  --set=step 3 incr
>>> backup  and target= step 2 time stamp .. It finished the pgaback restore
>>> and promote command
>>> 6. I am able to start back the  EPAS server and see the dropped table
>>> recovered there.
>>>
>>> But If I want a PTR as in the first section it fails.. Why ?
>>>
>>> Thank you,
>>> Krishane
>>>
>>>
>>>
>>>
>>>
>>> *What I have done and results obtained: *
>>>
>>> Created a table important_table4 in my EPAS and note down the time after
>>> creation of this table it is  ( t1 :  "01-AUG-24 14:08:32.447796+05:30" )
>>>
>>> Then I performed an Incremental backup   (incr backup:
>>> 20240729-160137F_20240801-141148I )
>>> timestamp start/stop: 2024-08-01 14:11:48+05:30 / 2024-08-01
>>> 14:11:52+05:30
>>>
>>>
>>> Now I dropped the table table4 from the EPAS and noted down the time
>>>
>>>
>>> I want to  restore the table4,, so I stopped EPAS and executed
>>>
>>> $ sudo -u enterprisedb pgbackrest --stanza=Demo_Repo --delta
>>> --set=20240729-160137F_20240801-141148I  --target-timeline=current
>>> --type=time  --target="01-AUG-24 14:08:32.447796+05:30"
>>> --target-action=promote restore
>>>
>>> IT WORKS AS EXPECTED .. after restarting the EPAS I am able to get the
>>> important_table4 back.
>>>
>>> root@service01 ~]# sudo -u enterprisedb psql edb
>>> psql (16.3.0)
>>> Type "help" for help.
>>>
>>> edb=# \dt
>>> List of relations
>>>  Schema |   Name   | Type  |Owner
>>> +--+---+--
>>>  public | important_table  | table | enterprisedb
>>>  public | important_table2 | table | enterprisedb
>>>  public | important_table3 | table | enterprisedb
>>>  public | important_table4 | table | enterprisedb
>>> (4 rows)
>>>
>>> SO all works fine  .
>>>
>>>
>>> *But Now the PROBLEM Statement. *
>>>
>>> *1. I am dropping the table table 4 again *
>>> edb=# \q
>>> [root@service01 ~]# sudo -u enterprisedb psql -c "begin; drop table
>>> important_table4; commit;" edb
>>> BEGIN
>>> DROP TABLE
>>> COMMIT
>>> *2 .  [root@service01 ~]#* sudo -u enterprisedb psql -Atc "select
>>> current_timestamp" edb  01-AUG-24 14:23:22.085076 +05:30
>>> Noting the time as :   (01-AUG-24 14:23:22.085076 +05:30 )
>>>
>>> 3. Now  I am performing an incremental backup after step 2  on REPO
>>> SErver ( Hoping that this latest INCR Backup is without dropped
>>> important_table4, so that a recovery of the cluster  shouldn't show the
>>> table4 again. )
>>>
>>> incr backup details. : 20240729-160137F_20240801-142433I
>>> timestamp start/stop*: 2024-08-01 14:24:33+05:30 /
>>> 2024-08-01 14:24:36+05:30*
>>>
>>> 4. Now I want to test the database recovery  after dropping the table4
>>> in step1 to verify that my EPAS restores from the backup in step 3 and time
>>> stamp (01-AUG-24 14:23:22.085076 +05:30,   so that  the restored EPAS
>>> cluster doesn't contain the important_table4.
>>>
>>> 5.  $ sudo -u enterpri

Re: some questions regarding replication issues and timeline/history files

[Mateusz Henicz]

Do you have "recovery_target_timeline=latest" configured in your
recovery.conf or postgresql.conf? Depending on the version you are using,
up to 11 recovery.conf and postgresql.conf 12+.

Cheers,
Mateusz

wt., 15 cze 2021, 22:05 użytkownik email2ssk...@gmail.com <
email2ssk...@gmail.com> napisał:

> Even I have this problem when I had to recover the database failed
> switchover.
> This is error is new primary server.
>
> < 2021-06-15 16:05:02.480 CEST > ERROR:  requested starting point
> AF/7D00 on timeline 1 is not in this server's history
> < 2021-06-15 16:05:02.480 CEST > DETAIL:  This server's history forked from
> timeline 1 at AF/7C0F8D58.
>
>
>
> --
> Sent from:
> https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
>
>
>

Re: PostgreSQL Database Community Edition -- Please provide list of the supported hypervisors

[Mateusz Henicz]

I think the correct answer will be "all of them" :D maybe besides DOSBOX,
etc. because there are no DOS packages.

Also, I do not think you should send "Confidential" emails to public
mailing lists :)

Cheers,
Mateusz

pon., 21 paź 2024 o 13:33 Sunil Kumar  napisał(a):

> Classification: *Confidential*
>
> Hi Team,
>
>
>
> Can you please provide list of the supported hypervisors to run the
> PostgreSQL Database.
>
>
>
> *Sunil Kumar*
>
> Mob: +91-9829753739
>
> OCI Architect
>
> *HCLTech*
>
> Supercharging Progress™
>
> hcltech.com 
>
> [image: Background pattern Description automatically generated with low
> confidence]
>
>
>
>
> ::DISCLAIMER::
> --
> The contents of this e-mail and any attachment(s) are confidential and
> intended for the named recipient(s) only. E-mail transmission is not
> guaranteed to be secure or error-free as information could be intercepted,
> corrupted, lost, destroyed, arrive late or incomplete, or may contain
> viruses in transmission. The e mail and its contents (with or without
> referred errors) shall therefore not attach any liability on the originator
> or HCL or its affiliates. Views or opinions, if any, presented in this
> email are solely those of the author and may not necessarily reflect the
> views or opinions of HCL or its affiliates. Any form of reproduction,
> dissemination, copying, disclosure, modification, distribution and / or
> publication of this message without the prior written consent of authorized
> representative of HCL is strictly prohibited. If you have received this
> email in error please delete it and notify the sender immediately. Before
> opening any email and/or attachments, please check them for viruses and
> other defects.
> --
>