RE: PostgreSQL 14.8 - server fails to start even though all transaction logs with base backup are restored
Hi Stephen,
The backups we ran were all exclusive backups.
pg_start_backup('label') returned 000400020041. Then we made a copy
of data directory .
Then pg_stop_backup() was executed and it returned 000400020046.
After this 2 databases were dropped and recreated (using dropdb and createdb).
Then we ran pg_switch_wal()
This returned 000400020049. Then we made a copy of the archive log
directory.
To verify the copied data is restorable, we stopped server. Renamed data and
archive log directories.
Replaced it with data and archive log directories copied earlier. In archive
log directory logs from 41 to 49 is present.
Created recovery.signal and added restore_command to postgresql.conf file. And
tried to start server. this failed with error in previous mail.
[root@gkstandby2 wal]# ls -ltr
total 262172
-rw--- 1 postgres postgres 42 Jun 13 01:08 0002.history
-rw--- 1 postgres postgres 16777216 Jun 22 22:50 00020002003B
-rw--- 1 postgres postgres 357 Jun 22 22:51
00020002003C.0028.backup
-rw--- 1 postgres postgres 16777216 Jun 22 22:51 00020002003C
-rw--- 1 postgres postgres 16777216 Jun 22 22:52 00020002003D
-rw--- 1 postgres postgres 357 Jun 22 22:52
00020002003E.0028.backup
-rw--- 1 postgres postgres 16777216 Jun 22 22:52 00020002003E
-rw--- 1 postgres postgres 16777216 Aug 4 14:47 0003000500E4
-rw--- 1 postgres postgres 16777216 Aug 4 14:48 00040002003F
-rw--- 1 postgres postgres 16777216 Aug 4 14:48 000400020040
-rw--- 1 postgres postgres 16777216 Aug 4 14:49 000400020041
-rw--- 1 postgres postgres 351 Aug 4 14:49
000400020040.0028.backup
-rw--- 1 postgres postgres 16777216 Aug 4 14:49 000400020042
-rw--- 1 postgres postgres 16777216 Aug 4 14:49 000400020043
-rw--- 1 postgres postgres 16777216 Aug 4 14:49 000400020044
-rw--- 1 postgres postgres 16777216 Aug 4 14:50 000400020045
-rw--- 1 postgres postgres 351 Aug 4 14:50
000400020043.0028.backup
-rw--- 1 postgres postgres 16777216 Aug 4 14:50 000400020046
-rw--- 1 postgres postgres 358 Aug 4 14:50
000400020045.5AE0.backup
-rw--- 1 postgres postgres 16777216 Aug 4 14:58 000400020047
-rw--- 1 postgres postgres 16777216 Aug 4 14:58 000400020048
-rw--- 1 postgres postgres 16777216 Aug 4 14:58 000400020049
[root@gkstandby2 wal]#
Regards,
Meera
-Original Message-
From: Stephen Frost mailto:sfr...@snowman.net>>
Sent: Tuesday, August 8, 2023 7:48 PM
To: Meera Nair mailto:mn...@commvault.com>>
Cc:
pgsql-general@lists.postgresql.org;
Punit Pranesh Koujalgi mailto:pkouja...@commvault.com>>
Subject: Re: PostgreSQL 14.8 - server fails to start even though all
transaction logs with base backup are restored
Greetings,
* Meera Nair (mn...@commvault.com) wrote:
> We are following
> https://www.postgresql.org/docs/14/continuous-archiving.html#BACKUP-LO
> WLEVEL-BASE-BACKUP
> Making An Exclusive Low-Level Backup.
Exclusive backups have been removed, so you really don't want to be depending
on it.
> After restoring, we see the server fails to start with below error:
Your archive doesn't seem to have all of the WAL which was generated during the
backup, which means that your backup wasn't complete and the system cannot be
restored from this backup.
> We are restoring all the transaction logs required for base backup to be
> consistent.
Evidently not, per the complaint from PG when it starts up.
> BACKUP_END is present in the 000400020046 segment. Why did
> recovery fail to find this?
It's possible to have multiple backups going concurrently. Presumably, this
was from a different backup and that's why it wasn't picked up as being the end
for this backup. You seem to have quite a few .backup files in your archive
directory which would seem to support this.
Not sure exactly what you're doing, but unless your goal is to spend a great
deal of effort developing a PG backup solution, you're really better off using
one of the existing solutions (eg: pg_basebackup or pgBackRest). If you are
developing your own backup solution for PG, you definitely want to be using the
new APIs and not using the exclusive backup method. Please review the current
(15) documentation:
https://www.postgresql.org/docs/current/continuous-archiving.html
Thanks,
Stephen
PrepareStatement and parameters data types
Hello, I was doing some experimenting to try and reduce the execution time of a query used in a PreparedStatement. To do that, I normally use the Prepare and Execute statements of Postgresql. What I notice is that explicit casting has a considerable performance impact. So using the good data types in the Prepare and avoid casting gives much better performance. My questions are : * Does the implementation of PreparedStatment in Postgresql driver uses the Prepare and Execute? * If so why I cannot see the Prepare and the parameters type in the logs if not how can I simulate the same mechanism from the command line? * Is it possible to avoid explicit casting without getting the error could not determine data type of parameter? The query is bit complex on large tables but the main part is : where cast($1 as text) is null or cast($2 as text) is null or ( date_field < cast(cast($3 as text) as date) and date_field > cast(cast($4 as text) as date) ) Thanks for your help. Sabiq
Re: PostgreSQL 14.8 - server fails to start even though all transaction logs with base backup are restored
Greetings,
* Meera Nair (mn...@commvault.com) wrote:
> The backups we ran were all exclusive backups.
You should not use exclusive backups. They've been deprecated for a
long, long time and have been removed in the most recent version of PG.
You really should not be trying to build your own tooling for this.
Please, use an existing tool where the authors have already worked
through how all of this works and understand how BACKUP_END works and
how to reliably perform a backup and restore of PG. Despite the
documentation seeming to imply that it's trivial to do this on your own,
it really isn't.
> pg_start_backup('label') returned 000400020041. Then we made a
> copy of data directory .
> Then pg_stop_backup() was executed and it returned 000400020046.
I'm really not sure what you're doing exactly, but it sure looks like
the BACKUP_END in the 46 WAL file is associated with the .backup file
for the backup started in the 45 segment. You have this file:
000400020045.5AE0.backup
and then this is what you reported from the 46 file:
rmgr: XLOGlen (rec/tot): 34/34, tx: 0, lsn:
2/46000110, prev 2/4698, desc: BACKUP_END 2/45005AE0
Note that the 'BACKUP_END' data content there is the starting point of
the backup for which that is the BACKUP_END for- 2/45005AE0. That
matches with a backup being started in the 45 file and matches the
start time of the .backup file that you have in the archive. That's why
the restore of the backup started in the 41 segment isn't picking up on
the BACKUP_END in the 46 file as being the one matching that backup-
because that's from some other backup.
> To verify the copied data is restorable, we stopped server. Renamed data and
> archive log directories.
> Replaced it with data and archive log directories copied earlier. In archive
> log directory logs from 41 to 49 is present.
The pg_wal dir can and should be empty at the start.
> Created recovery.signal and added restore_command to postgresql.conf file.
> And tried to start server. this failed with error in previous mail.
The restore_command should be copying WAL files from the location where
they were stored by the archive_command.
Thanks,
Stephen
signature.asc
Description: PGP signature
ignore_system_indexes=on no help for missing/corrupt pg_class_oid_index?
Originally posted in the Admin list but trying here to get more eyes on it :-) Greetings admins! I found that the ignore_system_indexes flag apparently doesn't get me past this case of a corrupt index as seen below. Or am I doing something silly here? The particular DB is a scratch so we'll just drop it but anyway, comments? I've not had to resort to this anytime lately but IIRC the ignore_system_indexes foo did the expected for us $sometime before. I believe that index 2662::regclass is probably this one but of course I had to probe for it in some other DB. The same one exists in template1 and I presume all other DBS as well. Please advise. Thx! pg is a bash alias for the full path to postgres of the matched version... $ pg --single -D $PWD -c config_file=$cf -c hba_file=$hba -P broken_db 1969183,2023-08-08 22:25:17.844 GMT,,XX000,64d2c0cd.1e0c1f,1,2023-08-08 22:25:17 GMT,1/1,0 || PANIC: could not open critical system index 2662 Aborted $ pg --single -D $PWD -c config_file=$cf -c hba_file=$hba -P postgres 1969691,2023-08-08 22:25:51.027 GMT,,0,64d2c0ee.1e0e1b,1,2023-08-08 22:25:50 GMT,1/0,0 || LOG: database system was interrupted; last known up at 2023-08-08 22:25:17 GMT 1969691,2023-08-08 22:25:51.045 GMT,,0,64d2c0ee.1e0e1b,2,2023-08-08 22:25:50 GMT,1/0,0 || LOG: database system was not properly shut down; automatic recovery in progress 1969691,2023-08-08 22:25:51.048 GMT,,0,64d2c0ee.1e0e1b,3,2023-08-08 22:25:50 GMT,1/0,0 || LOG: invalid record length at 0/5F175310: wanted 24, got 0 1969691,2023-08-08 22:25:51.048 GMT,,0,64d2c0ee.1e0e1b,4,2023-08-08 22:25:50 GMT,1/0,0 || LOG: redo is not required 1969691,2023-08-08 22:25:51.055 GMT,,0,64d2c0ee.1e0e1b,5,2023-08-08 22:25:50 GMT,1/0,0 || LOG: checkpoint starting: end-of-recovery immediate wait 1969691,2023-08-08 22:25:51.063 GMT,,0,64d2c0ee.1e0e1b,6,2023-08-08 22:25:50 GMT,1/0,0 || LOG: checkpoint complete: wrote 3 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.004 s, sync=0.001 s, total=0.010 s; sync files=2, longest=0.001 s, average=0.001 s; distance=0 kB, estimate=0 kB PostgreSQL stand-alone backend 15.3 (Ubuntu 15.3-1.pgdg20.04+1) backend> select relname from pg_class where oid=2662; 1969691,2023-08-08 22:26:11.183 GMT,,01000,64d2c0ee.1e0e1b,7,2023-08-08 22:25:50 GMT,1/2,0 || WARNING: using index "pg_toast_2619_index" despite IgnoreSystemIndexes 1: relname (typeid = 19, len = 64, typmod = -1, byval = f) 1: relname = "pg_class_oid_index" (typeid = 19, len = 64, typmod = -1, byval = f) backend> select version() 1: version (typeid = 25, len = -1, typmod = -1, byval = f) 1: version = "PostgreSQL 15.3 (Ubuntu 15.3-1.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit" (typeid = 25, len = -1, typmod = -1, byval = f) backend> show ignore_system_indexes 1: ignore_system_indexes (typeid = 25, len = -1, typmod = -1, byval = f) 1: ignore_system_indexes = "on"(typeid = 25, len = -1, typmod = -1, byval = f) backend>
Query regarding certificate authentication in postgres
Hello Sir/Madam, I have one query regarding client/server authentication using certificate in postgres. I am able to establish client server connection with OpenSSL certificate creation. We can create server and root certificates using OpenSSL and keep them in data directory. We need to update field in postgresql.conf (example ssl = ON, ssl_cert_file = "Server.crt", ssl_key_file = "Server.key", also for client we need to create certificate and key and need to update it in user directory. My query is, instead of using a physical certificate from a particular directory, can we use a certificate from the Windows certificate store for both server and client? Server will refer to the installed certificate from the server windows certificate store and the client will use the client windows certificate store. Please let me know if such a thing we can use. if yes , please share some references. Thanks and Regards, Ashok
