Re: Issue while creating index dynamically
On Thu, Jul 25, 2024 at 7:42 AM veem v wrote: > I was thinking the individual statement will work fine if I pull out those > from the begin..end block, as those will then be not bounded by any outer > transaction. > However, When I was trying it from dbeaver by extracting individual index > creation statements rather from within the "begin ..end;" block, it still > failed with a different error as below. Why is it so? > > "SQL Error [25001]: Error: create index concurrently cannot be executed > within a pipeline " Perhaps it's an artifact of DBeaver using [LibPQ's pipeline mode][1] when executing scripts? Maybe give it a try in `psql` instead. Or find a setting to disable pipeline mode? (which is new to v14). [1]: https://www.postgresql.org/docs/current/libpq-pipeline-mode.html
RE: Memory issues with PostgreSQL 15
Hi all, I started this discussion in May and was then dragged into other topics, so I could never follow up. Sorry for that! Since then, the problem has resurfaced from time to time. Right now, we seem to have issues again, which gives me the opportunity to follow up on your various suggestions. The current error messages are similar to what we have seen before: <2024-07-25 12:27:38 CEST - > LOG: could not fork autovacuum worker process: Cannot allocate memory <2024-07-25 12:27:38 CEST - mailprocessor> ERROR: could not resize shared memory segment "/PostgreSQL.1226901392" to 189280 bytes: No space left on device As far as I understand, it does not make much sense to look into SysV shared memory (which is what ipcs does). Indeed, there is only the same small shared memory segment as we have seen back then: # ipcs -m -- Shared Memory Segments keyshmid owner perms bytes nattch status 0x04000194 45 postgres 60056 20 Francisco and Tom both pointed at Posix shared memory instead; however, this also does not seem to be used a lot: # df -h /dev/shm Filesystem Size Used Avail Use% Mounted on tmpfs 7.8G 6.6M 7.8G 1% /dev/shm # ls -lhR /dev/shm /dev/shm: total 6.6M -rw--- 1 postgres postgres 1.0M Jul 25 06:26 PostgreSQL.1095217316 -rw--- 1 postgres postgres 1.0M Jul 23 06:20 PostgreSQL.124772332 -rw--- 1 postgres postgres 1.0M Jul 23 06:18 PostgreSQL.1475196260 -rw--- 1 postgres postgres 1.0M Jul 23 06:18 PostgreSQL.1725210234 -rw--- 1 postgres postgres 193K Jul 23 06:15 PostgreSQL.2581015990 -rw--- 1 postgres postgres 193K Jul 23 06:15 PostgreSQL.2929101952 -rw--- 1 postgres postgres 193K Jul 23 06:17 PostgreSQL.3018875836 -rw--- 1 postgres postgres 65K Jul 23 06:15 PostgreSQL.3403523208 -rw--- 1 postgres postgres 193K Jul 23 06:15 PostgreSQL.3482890896 -rw--- 1 postgres postgres 193K Jul 23 06:18 PostgreSQL.3824279998 -rw--- 1 postgres postgres 193K Jul 23 06:18 PostgreSQL.3891977516 -rw--- 1 postgres postgres 193K Jul 23 06:15 PostgreSQL.3929720846 -rw--- 1 postgres postgres 1.0M Jul 23 06:34 PostgreSQL.3969232506 -rw--- 1 postgres postgres 193K Jul 23 06:18 PostgreSQL.4222425006 We also still see a lot of available memory: # free -m totalusedfree shared buff/cache available Mem: 158826966 191210987256477 Swap: 1999 2711728 Again, exactly the same situation as before. Tom suggested that we hit some kernel limits, but I could not find any related kernel setting. The only limit I am aware of is the size of the /dev/shm filesystem itself. This could be changed, but the default value of 8 GB (which is half of the machine's memory) seems to be enough (given that it is not even used). Is there anything else I can analyze? Sorry again for reviving this old thread. Best, Christian PS: The database does not run in a Docker container. -Original Message- From: Tom Lane Sent: Wednesday, May 29, 2024 11:44 PM To: Christian Schröder Cc: Francisco Olarte ; pgsql-general@lists.postgresql.org; Eric Wong Subject: Re: Memory issues with PostgreSQL 15 [EXTERNAL] =?utf-8?B?Q2hyaXN0aWFuIFNjaHLDtmRlcg==?= writes: > # ipcs -m > -- Shared Memory Segments > keyshmid owner perms bytes nattch status > 0x04000194 35 postgres 60056 19 > I am surprised to see this since I would have expected much more shared > memory to be used by the database. Is there anything in the configuration > that prevents the shared memory from being used? SysV shared memory isn't that relevant to Postgres anymore. Most of what we allocate goes into POSIX-style shared memory segments, which are not shown by "ipcs". We do still create one small fixed-size data structure in SysV memory, which is what you're seeing here, for arcane reasons having to do with the lifespan of the shared memory segments being different in those two APIs. >> <2024-05-21 11:34:46 CEST - mailprocessor> ERROR: could not resize >> shared memory segment "/PostgreSQL.2448337832" to 182656 bytes: No >> space left on device This seems to indicate that you're hitting some kernel limit on the amount of POSIX shared memory. Not sure where to look for that. regards, tom lane -- SUPPORT: For any issues, inquiries, or assistance, please contact our support team at supp...@wsd.com. Our dedicated team is available to help you and provide prompt assistance. CONFIDENTIALITY NOTICE: This email and any attachments are confidential and intended solely for the use of the individual or entity to whom it is addressed. If you have received this email in error, please notify the sender immediately and delete it from your system.
Re: Memory issues with PostgreSQL 15
On Thu, 2024-07-25 at 10:58 +, Christian Schröder wrote: > The current error messages are similar to what we have seen before: > > <2024-07-25 12:27:38 CEST - > LOG: could not fork autovacuum worker process: > Cannot allocate memory > <2024-07-25 12:27:38 CEST - mailprocessor> ERROR: could not resize shared > memory segment "/PostgreSQL.1226901392" to 189280 bytes: No space left on > device > > As far as I understand, it does not make much sense to look into SysV shared > memory (which is what ipcs does). Indeed, there is only the same small shared > memory segment as we have seen back then: > > [...] > > Francisco and Tom both pointed at Posix shared memory instead; however, this > also does not seem to be used a lot: > > # df -h /dev/shm > Filesystem Size Used Avail Use% Mounted on > tmpfs 7.8G 6.6M 7.8G 1% /dev/shm > > We also still see a lot of available memory: > > # free -m > total used free shared buff/cache > available > Mem: 15882 6966 191 2109 8725 > 6477 > Swap: 1999 271 1728 > > Again, exactly the same situation as before. > > Tom suggested that we hit some kernel limits, but I could not find any related > kernel setting. The only limit I am aware of is the size of the /dev/shm > filesystem > itself. This could be changed, but the default value of 8 GB (which is half of > the machine's memory) seems to be enough (given that it is not even used). > > Is there anything else I can analyze? Sorry again for reviving this old > thread. It could be dynamic shared memory segments created temporarily during parallel query execution. Try setting "max_parallel_workers_per_gather = 0", that should make that problem disappear. Yours, Laurenz Albe
Re: Memory issues with PostgreSQL 15
> On 25 Jul 2024, at 12:58, Christian Schröder > wrote: > > Hi all, > I started this discussion in May and was then dragged into other topics, so I > could never follow up. Sorry for that! > Since then, the problem has resurfaced from time to time. Right now, we seem > to have issues again, which gives me the opportunity to follow up on your > various suggestions. > > The current error messages are similar to what we have seen before: > > <2024-07-25 12:27:38 CEST - > LOG: could not fork autovacuum worker process: > Cannot allocate memory > <2024-07-25 12:27:38 CEST - mailprocessor> ERROR: could not resize shared > memory segment "/PostgreSQL.1226901392" to 189280 bytes: No space left on > device We sometimes encounter a similar issue, but with disk space - on a 1TB virtual disk of which usually only about 1/4th is in use. Our hypothesis is that sometimes some long-running transactions need to process a lot of data and put so much of it in temporary tables that they fill up the remaining space. We’ve seen the disk space climb and hit the ’No space left on device’ mark - at which point the transactions get aborted and rolled back, putting us back at the 1/4th of space in use situation. Have you been able to catch your shared memory shortage in the act? I suspect that the stats you showed in your message were those after rollback. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest.
Re: Memory issues with PostgreSQL 15
On Thu, Jul 25, 2024 at 6:59 AM Christian Schröder < christian.schroe...@wsd.com> wrote: > Hi all, > I started this discussion in May and was then dragged into other topics, > so I could never follow up. Sorry for that! > Since then, the problem has resurfaced from time to time. Right now, we > seem to have issues again, which gives me the opportunity to follow up on > your various suggestions. > > The current error messages are similar to what we have seen before: > > <2024-07-25 12:27:38 CEST - > LOG: could not fork autovacuum worker > process: Cannot allocate memory > <2024-07-25 12:27:38 CEST - mailprocessor> ERROR: could not resize shared > memory segment "/PostgreSQL.1226901392" to 189280 bytes: No space left on > device > What's mailprocessor? Maybe it's using some tmpfs device. > >
PgbackRest PointTIme Recovery : server unable to start back
List,
Reference: https://pgbackrest.org/user-guide-rhel.html#pitr
I am following the PTR on RHEL9 EPAS16.
I am able to do a backup(Full, diff and incr) and restore from a full
backup and restart of EPAS16 works fine.
But when I do an incremental backup after doing the procedures mentioned
in the PTR section of the above reference link and try restoring the EDB
database from the INCR backup and then starting up the EPAS16 the
server always results in dead state
My repo server is another machine. If I do a full restore on the DB
server ( sudo -u enterprisedb pgbackrest --stanza=Demo_Repo --delta
restore) it works and the server starts without any issue.
Restoring from Incremental backup tty output shows successful but edb
service start results in failure.
Any help is much appreciated.
Krishane.
STEPS followed:
after dropping the table pg-primary *⇒* Drop the important table
([section]
stopped the EDB server.
$ sudo -u enterprisedb pgbackrest --stanza=Demo_Repo--delta
--set=20240719-122703F_20240724-094727I --target-timeline=current
--type=time "--target=2024-07-24 09:44:01.3255+05:30"
--target-action=promote restore
.
2024-07-26 09:48:06.343 P00 INFO: restore command end: completed
successfully (1035ms)
But
[root@rservice01 ~]# sudo systemctl start edb-as-16.service
[root@service01 ~]# sudo systemctl status edb-as-16.service
× edb-as-16.service - EDB Postgres Advanced Server 16
Loaded: loaded (/etc/systemd/system/edb-as-16.service; disabled;
preset: disabled)
* Active: failed* (Result: exit-code) since Fri 2024-07-26 09:48:50
IST; 8s ago
Duration: 242ms
Process: 41903 ExecStartPre=/usr/edb/as16/bin/edb-as-16-check-db-dir
${PGDATA} (code=exited, status=0/SUCCESS)
Process: 41908 ExecStart=/usr/edb/as16/bin/edb-postgres -D ${PGDATA}
(code=exited, status=1/FAILURE)
Main PID: 41908 (code=exited, status=1/FAILURE)
CPU: 331ms
Jul 26 09:48:48 service01 systemd[1]: Starting EDB Postgres Advanced Server
16...
Jul 26 09:48:48 service01 edb-postgres[41908]: 2024-07-26 09:48:48 IST LOG:
redirecting log output to logging collector process
Jul 26 09:48:48 service01 edb-postgres[41908]: 2024-07-26 09:48:48 IST
HINT: Future log output will appear in directory "log".
Jul 26 09:48:49 service01 systemd[1]: Started EDB Postgres Advanced Server
16.
Jul 26 09:48:50 service01 systemd[1]: edb-as-16.service: Main process
exited, code=exited, status=1/FAILURE
Jul 26 09:48:50 service01 systemd[1]: edb-as-16.service: Killing process
41909 (edb-postgres) with signal SIGKILL.
Jul 26 09:48:50 service01 systemd[1]: edb-as-16.service: Failed with result
'exit-code'.
[root@service01 ~]#
why is it unable to perform a restore and recovery from an incr backup ?
On The Repo Server
[root@service02 ~]# sudo -u postgres pgbackrest --stanza=Demo_Repo info
stanza: Demo_Repo
status: ok
cipher: aes-256-cbc
db (current)
wal archive min/max (16):
00020021/000B0041
full backup: 20240719-122703F
timestamp start/stop: 2024-07-19 12:27:03+05:30 / 2024-07-19
12:27:06+05:30
wal start/stop: 0002002A /
0002002A
database size: 61.7MB, database backup size: 61.7MB
repo1: backup size: 9.6MB
incr backup: 20240719-122703F_20240719-123353I
timestamp start/stop: 2024-07-19 12:33:53+05:30 / 2024-07-19
12:33:56+05:30
wal start/stop: 0002002C /
0002002C
database size: 61.7MB, database backup size: 6.4MB
repo1: backup size: 6.2KB
backup reference list: 20240719-122703F
diff backup: 20240719-122703F_20240719-123408D
timestamp start/stop: 2024-07-19 12:34:08+05:30 / 2024-07-19
12:34:10+05:30
wal start/stop: 0002002E /
0002002E
database size: 61.7MB, database backup size: 6.4MB
repo1: backup size: 6.4KB
backup reference list: 20240719-122703F
incr backup: 20240719-122703F_20240723-110212I
timestamp start/stop: 2024-07-23 11:02:12+05:30 / 2024-07-23
11:02:15+05:30
wal start/stop: 00070038 /
00070038
database size: 48MB, database backup size: 6.4MB
repo1: backup size: 9.8KB
backup reference list: 20240719-122703F,
20240719-122703F_20240719-123408D
incr backup: 20240719-122703F_20240723-141818I
timestamp start/stop: 2024-07-23 14:18:18+05:30 / 2024-07-23
14:18:22+05:30
wal start/stop: 0008003C /
0008003C
database size: 75.4MB, database backup size: 33.8MB
repo1: backup size: 4.7MB
backup reference list: 20240719-122703F,
20240719-122703F_20240719-123408D, 20240719-122703F_20240723-110212I
Re: PgbackRest PointTIme Recovery : server unable to start back
Hi KK CHN
Could you check server logs ?
Your service trace suggests that it started server and then failure happened
ul 26 09:48:49 service01 systemd[1]: Started EDB Postgres Advanced Server
16.
Jul 26 09:48:50 service01 systemd[1]: edb-as-16.service: Main process
exited, code=exited, status=1/FAILURE
Regards,
Ikram
On Fri, Jul 26, 2024 at 10:04 AM KK CHN wrote:
> List,
>
> Reference: https://pgbackrest.org/user-guide-rhel.html#pitr
> I am following the PTR on RHEL9 EPAS16.
> I am able to do a backup(Full, diff and incr) and restore from a full
> backup and restart of EPAS16 works fine.
>
> But when I do an incremental backup after doing the procedures
> mentioned in the PTR section of the above reference link and try
> restoring the EDB database from the INCR backup and then starting up the
> EPAS16 the server always results in dead state
>
> My repo server is another machine. If I do a full restore on the DB
> server ( sudo -u enterprisedb pgbackrest --stanza=Demo_Repo --delta
> restore) it works and the server starts without any issue.
> Restoring from Incremental backup tty output shows successful but edb
> service start results in failure.
>
> Any help is much appreciated.
>
> Krishane.
>
>
>
>
> STEPS followed:
>
> after dropping the table pg-primary *⇒* Drop the important table
> ([section]
> stopped the EDB server.
>
> $ sudo -u enterprisedb pgbackrest --stanza=Demo_Repo--delta
> --set=20240719-122703F_20240724-094727I --target-timeline=current
> --type=time "--target=2024-07-24 09:44:01.3255+05:30"
> --target-action=promote restore
> .
>
> 2024-07-26 09:48:06.343 P00 INFO: restore command end: completed
> successfully (1035ms)
>
>
> But
>
> [root@rservice01 ~]# sudo systemctl start edb-as-16.service
> [root@service01 ~]# sudo systemctl status edb-as-16.service
> × edb-as-16.service - EDB Postgres Advanced Server 16
> Loaded: loaded (/etc/systemd/system/edb-as-16.service; disabled;
> preset: disabled)
>* Active: failed* (Result: exit-code) since Fri 2024-07-26 09:48:50
> IST; 8s ago
>Duration: 242ms
> Process: 41903 ExecStartPre=/usr/edb/as16/bin/edb-as-16-check-db-dir
> ${PGDATA} (code=exited, status=0/SUCCESS)
> Process: 41908 ExecStart=/usr/edb/as16/bin/edb-postgres -D ${PGDATA}
> (code=exited, status=1/FAILURE)
>Main PID: 41908 (code=exited, status=1/FAILURE)
> CPU: 331ms
>
> Jul 26 09:48:48 service01 systemd[1]: Starting EDB Postgres Advanced
> Server 16...
> Jul 26 09:48:48 service01 edb-postgres[41908]: 2024-07-26 09:48:48 IST
> LOG: redirecting log output to logging collector process
> Jul 26 09:48:48 service01 edb-postgres[41908]: 2024-07-26 09:48:48 IST
> HINT: Future log output will appear in directory "log".
> Jul 26 09:48:49 service01 systemd[1]: Started EDB Postgres Advanced Server
> 16.
> Jul 26 09:48:50 service01 systemd[1]: edb-as-16.service: Main process
> exited, code=exited, status=1/FAILURE
> Jul 26 09:48:50 service01 systemd[1]: edb-as-16.service: Killing process
> 41909 (edb-postgres) with signal SIGKILL.
> Jul 26 09:48:50 service01 systemd[1]: edb-as-16.service: Failed with
> result 'exit-code'.
> [root@service01 ~]#
>
> why is it unable to perform a restore and recovery from an incr backup ?
>
>
>
>
>
>
>
> On The Repo Server
> [root@service02 ~]# sudo -u postgres pgbackrest --stanza=Demo_Repo info
> stanza: Demo_Repo
> status: ok
> cipher: aes-256-cbc
>
> db (current)
> wal archive min/max (16):
> 00020021/000B0041
>
> full backup: 20240719-122703F
> timestamp start/stop: 2024-07-19 12:27:03+05:30 / 2024-07-19
> 12:27:06+05:30
> wal start/stop: 0002002A /
> 0002002A
> database size: 61.7MB, database backup size: 61.7MB
> repo1: backup size: 9.6MB
>
> incr backup: 20240719-122703F_20240719-123353I
> timestamp start/stop: 2024-07-19 12:33:53+05:30 / 2024-07-19
> 12:33:56+05:30
> wal start/stop: 0002002C /
> 0002002C
> database size: 61.7MB, database backup size: 6.4MB
> repo1: backup size: 6.2KB
> backup reference list: 20240719-122703F
>
> diff backup: 20240719-122703F_20240719-123408D
> timestamp start/stop: 2024-07-19 12:34:08+05:30 / 2024-07-19
> 12:34:10+05:30
> wal start/stop: 0002002E /
> 0002002E
> database size: 61.7MB, database backup size: 6.4MB
> repo1: backup size: 6.4KB
> backup reference list: 20240719-122703F
>
> incr backup: 20240719-122703F_20240723-110212I
> timestamp start/stop: 2024-07-23 11:02:12+05:30 / 2024-07-23
> 11:02:15+05:30
> wal start/stop: 00070038 /
> 00070038
> database size: 48MB, database backup
Re: Slow performance
Hello,Using PG 11.11, One PG Cluster, Windows 2019 Server Standard, Two
databases with identical data.
1. First DB: client_db
2. Second DB: client_test
Took backup (pg_dump) of first database (client_db) and restored the database
as second database (client_test).
Query:
Select a.examname, a.registrationnumber, b.studentname, d.departmentname,
e.levelname, a.subjectcode, c.subjectname, b.regular, a.semester,
a.dummynumber, p.semester as curr_sem, a.internalmark, a.externalmark,
a.result, coalesce((a.internalmark + a.externalmark),0) as total,
a.absent, a.malpractice, c.maxinternalmark, f.noofsemester,
a.examstudentstatus, Case When a.result = 'P' Then 'P' Else
Case When a.result = 'F' and a.malpractice = 'Y' and a.examstudentstatus is
null Then 'M' Else Case When a.result = 'F' and a.absent = 'Y' and
a.examstudentstatus = 'R' Then 'R.C' Else Case When a.result = 'F' and
a.absent = 'Y' and a.examstudentstatus = 'S' Then 'S.L' Else Case When
a.result = 'F' and a.absent = 'Y' and a.examstudentstatus = 'F' Then 'N.P' Else
Case When a.result = 'F' and a.absent = 'Y' and a.examstudentstatus =
'W' Then 'W.H' Else 'RA'
End End End End End End as res, Concat(RTrim(f.degreeawarded), ' ',
RTrim(d.departmentname)) as course, a.revaluation, m.absent as
int_abs, n.companyname, n.companydescription, m.totalmark as int_mark,
q.addressone, q.addresstwo, Case When a.semester > f.noofsemester Then
'PRIVATE' When a.semester <= f.noofsemester and a.semester in
(1,2) Then 'I - Year' When a.semester <= f.noofsemester and
a.semester in (3,4) Then 'II - Year' When a.semester <=
f.noofsemester and a.semester in (5,6) Then 'III - Year' End as studyr,
c.subjectserialno, p.regulararrear From cl_student_exam_subject a Join
cl_student_name b On b.companycode = a.companycode And
b.registrationnumber = a.registrationnumber Join cl_subject c On
c.companycode = a.companycode And c.subjectcode = a.subjectcode Join
cl_department_header d On d.departmentheaderpk = b.departmentheaderfk Join
cl_level e On e.levelpk = b.Levelfk Join cl_department_detail f On
f.departmentheaderfk = b.departmentheaderfk And f.levelfk = b.levelfk Left
Outer Join cl_student_internal_mark m On m.companycode = a.companycode And
m.registrationnumber = a.registrationnumber And m.subjectcode =
a.subjectcode And m.departmentheaderfk = b.departmentheaderfk And
m.levelfk = b.levelfk And m.Regular = b.Regular Join co_company n On
n.companycode = a.companycode Join cl_student_semester_subject p On
p.companycode = a.companycode And p.examheaderfk = a.examheaderfk And
p.subjectcode = a.subjectcode And p.registrationnumber =
a.registrationnumber And p.semester = a.semester Join co_company_branch q
On n.companycode = a.companycode Where a.companycode = '100' And
a.examheaderfk = 'BA80952CFF8F4E1C3F9F44B62ED9BF37' And (a.examstudentstatus
is null or a.examstudentstatus in ('R', 'S', 'W')) And b.departmentheaderfk
in ('04DF8BD89D0844DD4D8AA151EFB28657') And b.levelfk in
('37A9BEC2638844FFD5B1422D83E70EF3') And b.status = 'A' Order By
Concat(RTrim(f.degreeawarded), ' ', RTrim(d.departmentname)), Case
When a.semester > f.noofsemester Then 'PRIVATE' When a.semester
<= f.noofsemester and a.semester in (1,2) Then 'I - Year' When
a.semester <= f.noofsemester and a.semester in (3,4) Then 'II - Year'
When a.semester <= f.noofsemester and a.semester in (5,6) Then 'III -
Year' End, a.registrationnumber, b.regular, p.semester desc,
c.subjectserialno, Case When c.subjectcategory = 'T' Then 1 When
c.subjectcategory = 'P' Then 2 When c.subjectcategory = 'D' Then 3 When
c.subjectcategory = 'V' Then 4 When c.subjectcategory = 'J' Then 5 End,
c.ancillary, Case When Substring(a.subjectcode, 6, 1) = 'C' Then 1
When Substring(a.subjectcode, 6, 1) = 'S' Then 2 When
Substring(a.subjectcode, 6, 1) = 'A' Then 3 When
Substring(a.subjectcode, 6, 1) = 'E' Then 4 Else 5 End, a.subjectcode
Explain Analyze of DB 1 (client_db) :"Sort (cost=2862.35..2862.36 rows=1
width=1088) (actual time=451671.464..451671.495 rows=326 loops=1)"" Sort Key:
(concat(rtrim((f.degreeawarded)::text), ' ', rtrim((d.departmentname)::text))),
(CASE WHEN (a.semester > f.noofsemester) THEN 'PRIVATE'::text WHEN ((a.semester
<= f.noofsemester) AND (a.semester = ANY ('{1,2}'::integer[]))) THEN 'I -
Year'::text WHEN ((a.semester <= f.noofsemester) AND (a.semester = ANY
('{3,4}'::integer[]))) THEN 'II - Year'::text WHEN ((a.semester <=
f.noofsemester) AND (a.semester = ANY ('{5,6}'::integer[]))) THEN 'III -
Year'::text ELSE NULL::text END), a.registrationnum
Re: Slow performance
Hi,
Could you perform diff on postgresql.conf file to see whether values are
same for work_mem, shared_buffers, maintenance_work_mem and other related
parameters?
Regards,
Ikram
On Fri, Jul 26, 2024 at 10:31 AM sivapostg...@yahoo.com <
sivapostg...@yahoo.com> wrote:
> Hello,
> Using PG 11.11, One PG Cluster, Windows 2019 Server Standard, Two
> databases with identical data.
>
> 1. First DB: client_db
> 2. Second DB: client_test
>
> Took backup (pg_dump) of first database (client_db) and restored the
> database as second database (client_test).
>
> Query:
> Select a.examname, a.registrationnumber, b.studentname, d.departmentname,
> e.levelname,
> a.subjectcode, c.subjectname, b.regular, a.semester,
> a.dummynumber, p.semester as curr_sem,
> a.internalmark, a.externalmark, a.result, coalesce((a.internalmark
> + a.externalmark),0) as total,
> a.absent, a.malpractice, c.maxinternalmark, f.noofsemester,
> a.examstudentstatus,
> Case When a.result = 'P' Then 'P' Else
> Case When a.result = 'F' and a.malpractice = 'Y' and
> a.examstudentstatus is null Then 'M' Else
> Case When a.result = 'F' and a.absent = 'Y' and
> a.examstudentstatus = 'R' Then 'R.C' Else
> Case When a.result = 'F' and a.absent = 'Y' and
> a.examstudentstatus = 'S' Then 'S.L' Else
> Case When a.result = 'F' and a.absent = 'Y' and
> a.examstudentstatus = 'F' Then 'N.P' Else
> Case When a.result = 'F' and a.absent = 'Y' and
> a.examstudentstatus = 'W' Then 'W.H' Else
> 'RA' End End End End
> End End as res,
> Concat(RTrim(f.degreeawarded), ' ', RTrim(d.departmentname)) as
> course,
> a.revaluation, m.absent as int_abs, n.companyname,
> n.companydescription,
> m.totalmark as int_mark, q.addressone, q.addresstwo,
> Case When a.semester > f.noofsemester Then 'PRIVATE'
> When a.semester <= f.noofsemester and a.semester in (1,2)
> Then 'I - Year'
> When a.semester <= f.noofsemester and a.semester in (3,4)
> Then 'II - Year'
> When a.semester <= f.noofsemester and a.semester in (5,6)
> Then 'III - Year' End as studyr,
>c.subjectserialno, p.regulararrear
> From cl_student_exam_subject a
> Join cl_student_name b
> Onb.companycode = a.companycode
> And b.registrationnumber = a.registrationnumber
> Join cl_subject c
> Onc.companycode = a.companycode
> And c.subjectcode= a.subjectcode
> Join cl_department_header d
> On d.departmentheaderpk = b.departmentheaderfk
> Join cl_level e
> On e.levelpk = b.Levelfk
> Join cl_department_detail f
> On f.departmentheaderfk = b.departmentheaderfk
> And f.levelfk = b.levelfk
> Left Outer Join cl_student_internal_mark m
> Onm.companycode = a.companycode
> And m.registrationnumber = a.registrationnumber
> And m.subjectcode = a.subjectcode
> And m.departmentheaderfk = b.departmentheaderfk
> And m.levelfk = b.levelfk
> And m.Regular = b.Regular
> Join co_company n
> Onn.companycode = a.companycode
> Join cl_student_semester_subject p
> Onp.companycode = a.companycode
> And p.examheaderfk = a.examheaderfk
> And p.subjectcode = a.subjectcode
> And p.registrationnumber = a.registrationnumber
> And p.semester = a.semester
> Join co_company_branch q
> Onn.companycode = a.companycode
> Where a.companycode = '100'
> Anda.examheaderfk = 'BA80952CFF8F4E1C3F9F44B62ED9BF37'
> And (a.examstudentstatus is null or a.examstudentstatus in ('R', 'S',
> 'W'))
> And b.departmentheaderfk in ('04DF8BD89D0844DD4D8AA151EFB28657')
> Andb.levelfk in ('37A9BEC2638844FFD5B1422D83E70EF3')
> Andb.status = 'A'
> Order By Concat(RTrim(f.degreeawarded), ' ', RTrim(d.departmentname)),
> Case When a.semester > f.noofsemester Then 'PRIVATE'
>When a.semester <= f.noofsemester and a.semester in (1,2)
> Then 'I - Year'
>When a.semester <= f.noofsemester and a.semester in (3,4)
> Then 'II - Year'
>When a.semester <= f.noofsemester and a.semester in (5,6)
> Then 'III - Year' End,
> a.registrationnumber, b.regular, p.semester desc,
> c.subjectserialno,
> Case When c.subjectcategory = 'T' Then 1
> When c.subjectcategory = 'P' Then 2
> When c.subjectcategory = 'D' Then 3
> When c.subjectcategory = 'V' Then 4
> When c.subjectcategory = 'J' Then 5 End,
> c.ancillary,
> Case When Substring(a.subjectcode, 6, 1) = 'C' Then 1
>When Substring(a.subjectcode, 6, 1) = 'S' Then 2
>When Substring(a.subjectcode, 6, 1) = 'A' Then 3
>When Substring(a.subjectcode, 6, 1) = 'E' Then 4 Else 5 End,
> a.subjectcode
>
>
> Explain Analyze of DB 1 (client_db) :
> "Sort (cost=2862.35..2862.36 rows=1 width=1088) (actual
> time=451671.464..451671.495 rows=326 loops=1)"
> " Sort Key: (concat(rtrim((f.degreeawarded)::text), ' ',
> rtrim((d.depart
Re: PgbackRest and EDB Query
On Fri, Jul 19, 2024 at 5:59 PM KK CHN wrote: > Hi list, > > Thank you all for the great help and guidance, I am able to configure > pgbackrest with EPAS-16 and a Repo server both separate machines.. > Password less auth also worked well. Backup and restore all fine. > > Query > How can I make the Reposerver to host more than one EPAS-16 server > instance's running on multiple nodes ? > Yes, You have to add different stanzas for different servers. Regards Kashif Zeeshan > > Having only one /etg/pgbakrest/pgbackrest.conf file on the Repo Server > how to specify stanza name and global for multiple EPAS servers? > My Repo Server: cat /etc/pgbackrest/pgbackrest.conf > > [Demo_Repo] > pg1-host=10.20.20.6 > pg1-host-user=enterprisedb > pg1-path=/var/lib/edb/as16/data > pg-version-force=16 > > [global] > about the repository > > repo1-path=/var/lib/edb_BackupRepo > > repo1-retention-full=2 > repo1-cipher-type=aes-256-cbc > > repo1-cipher-pass=0oahu5f5dvH7eD4TI1eBEl8Vpn14hWEmgLGuXgpUHo9R2VQKCw6Sm99FnOfHBY > process-max=5 > log-level-console=info > log-level-file=debug > start-fast=y > delta=y > repo1-block=y > repo1-bundle=y > [global:archive-push] > compress-level=3 > ## > > > 1. So if there are multiple EPAS servers running ondifferent > nodes 10.20.20.7, 10.20.20.8, etc. how to specify the stanzas and > globals for each EPAS server in single ( /etc/pgbackrest/pgbackrest.conf > )on Repo server ? > > 2. Say there are X numbers (say 10 EPAS servers from different geo > locations) of EPAS servers each has a daily growth of aprox 1 GB/day > then what should be the connectivity capacity parameters need to > consider to cater the archiving and replication by pgbackrest in a > production environment to the repo server ? > > 3. Also what will be the best backup configuration in a crontab for > achieving maximum RPO ? I mean zero data loss ?( incr or diff > repetition intervals ?) here my sample crontab, only for full and diff > (in lab setup) but for production env and for near zero data lost what > configs needed in cron ? > > my sample cron here. > [root@RepoServer ~]# crontab -u postgres -l > 30 06 * * 0 pgbackrest --type=full--stanza=Demo2 backup > // only on sundays > > 04 16 * * 1-6 pgbackrest --type=diff --stanza=Demo2backup > // on everyday diff > [root@uaterssdrservice02 ~]# > > Thanks again > Krishane > > > On Fri, Jul 19, 2024 at 11:24 AM azeem subhani > wrote: > >> Hi, >> >> passwordless connection can be established using ssh key, and when you >> don't specify the ssh key in command using -i switch:* -i >> /path/to/your/private/key* >> You simply need to set the SSH key as the default key which I have >> explained earlier, how to do that. >> >> As you are currently trying through following command, without specifying >> an ssh key for passwordless connection. >> >> From the EDB Postgres Advanced Server nodes >> $ sudo -u enterprisedb ssh pgbackrest@backup-server >> >> >> >> >> On Fri, Jul 19, 2024 at 10:06 AM Kashif Zeeshan >> wrote: >> >>> Hi >>> >>> On Thu, Jul 18, 2024 at 6:10 PM KK CHN wrote: >>> Hi list, Thank you all for your inputs, I am trying pgbacrest with Enterprised DB. Locally pgbackrest works for EDB but when I am trying for remote repository I am facing an issue ( from the remote host to EDB server password less authentication part ) Trying to use a remote host as Repo Server I am facing the issue of passwordless authentication(Public key private key). 1. From the EDB server I added the user pgbackrest directory and generated ssh-keys and copied the id_rsa.pub to the Repo server (pgbackrest user's .ssh dir with necessary permissions) everything(passwordless auth) working to one side. From the EDB Postgres Advanced Server nodes $ sudo -u enterprisedb ssh pgbackrest@backup-server This works from EDB server machine without any issue(password less auth works) 2 But from the reposerver $sudo -u pgbackrest ssh enterprisedb@EDB_Server_IP unable to do password less auth( Its asking password for enterpridb@EDB_Server ) How to do the passwordless auth from the Repo server to the EDB server for the default "enterprisedb" user of EDB ? ( enterprisedb user doesn't have any home dir I mean /home/enterprisedb, so I am not sure where to create .ssh dir and authorized_keys for passwordless auth ) >>> Please make sure that the passwordless connection is made between both >>> from EDB Server to Repo Server and from Repo Server to EDB Server. >>> For this you need to generate the ssh keys on both EDB server abd Repo >>> Servers and copy the id_rsa.pub from EDB Server to Repo Server
Re: Slow performance
I've only one instance of PG in that server. Means only one postgresql.conf
for both databases.
On Friday, 26 July, 2024 at 11:12:34 am IST, Muhammad Ikram
wrote:
Hi,
Could you perform diff on postgresql.conf file to see whether values are same
for work_mem, shared_buffers, maintenance_work_mem and other related parameters?
Regards,Ikram
On Fri, Jul 26, 2024 at 10:31 AM sivapostg...@yahoo.com
wrote:
Hello,Using PG 11.11, One PG Cluster, Windows 2019 Server Standard, Two
databases with identical data.
1. First DB: client_db
2. Second DB: client_test
Took backup (pg_dump) of first database (client_db) and restored the database
as second database (client_test).
Query:
Select a.examname, a.registrationnumber, b.studentname, d.departmentname,
e.levelname, a.subjectcode, c.subjectname, b.regular, a.semester,
a.dummynumber, p.semester as curr_sem, a.internalmark, a.externalmark,
a.result, coalesce((a.internalmark + a.externalmark),0) as total,
a.absent, a.malpractice, c.maxinternalmark, f.noofsemester,
a.examstudentstatus, Case When a.result = 'P' Then 'P' Else
Case When a.result = 'F' and a.malpractice = 'Y' and a.examstudentstatus is
null Then 'M' Else Case When a.result = 'F' and a.absent = 'Y' and
a.examstudentstatus = 'R' Then 'R.C' Else Case When a.result = 'F' and
a.absent = 'Y' and a.examstudentstatus = 'S' Then 'S.L' Else Case When
a.result = 'F' and a.absent = 'Y' and a.examstudentstatus = 'F' Then 'N.P' Else
Case When a.result = 'F' and a.absent = 'Y' and a.examstudentstatus =
'W' Then 'W.H' Else 'RA'
End End End End End End as res, Concat(RTrim(f.degreeawarded), ' ',
RTrim(d.departmentname)) as course, a.revaluation, m.absent as
int_abs, n.companyname, n.companydescription, m.totalmark as int_mark,
q.addressone, q.addresstwo, Case When a.semester > f.noofsemester Then
'PRIVATE' When a.semester <= f.noofsemester and a.semester in
(1,2) Then 'I - Year' When a.semester <= f.noofsemester and
a.semester in (3,4) Then 'II - Year' When a.semester <=
f.noofsemester and a.semester in (5,6) Then 'III - Year' End as studyr,
c.subjectserialno, p.regulararrear From cl_student_exam_subject a Join
cl_student_name b On b.companycode = a.companycode And
b.registrationnumber = a.registrationnumber Join cl_subject c On
c.companycode = a.companycode And c.subjectcode = a.subjectcode Join
cl_department_header d On d.departmentheaderpk = b.departmentheaderfk Join
cl_level e On e.levelpk = b.Levelfk Join cl_department_detail f On
f.departmentheaderfk = b.departmentheaderfk And f.levelfk = b.levelfk Left
Outer Join cl_student_internal_mark m On m.companycode = a.companycode And
m.registrationnumber = a.registrationnumber And m.subjectcode =
a.subjectcode And m.departmentheaderfk = b.departmentheaderfk And
m.levelfk = b.levelfk And m.Regular = b.Regular Join co_company n On
n.companycode = a.companycode Join cl_student_semester_subject p On
p.companycode = a.companycode And p.examheaderfk = a.examheaderfk And
p.subjectcode = a.subjectcode And p.registrationnumber =
a.registrationnumber And p.semester = a.semester Join co_company_branch q
On n.companycode = a.companycode Where a.companycode = '100' And
a.examheaderfk = 'BA80952CFF8F4E1C3F9F44B62ED9BF37' And (a.examstudentstatus
is null or a.examstudentstatus in ('R', 'S', 'W')) And b.departmentheaderfk
in ('04DF8BD89D0844DD4D8AA151EFB28657') And b.levelfk in
('37A9BEC2638844FFD5B1422D83E70EF3') And b.status = 'A' Order By
Concat(RTrim(f.degreeawarded), ' ', RTrim(d.departmentname)), Case
When a.semester > f.noofsemester Then 'PRIVATE' When a.semester
<= f.noofsemester and a.semester in (1,2) Then 'I - Year' When
a.semester <= f.noofsemester and a.semester in (3,4) Then 'II - Year'
When a.semester <= f.noofsemester and a.semester in (5,6) Then 'III -
Year' End, a.registrationnumber, b.regular, p.semester desc,
c.subjectserialno, Case When c.subjectcategory = 'T' Then 1 When
c.subjectcategory = 'P' Then 2 When c.subjectcategory = 'D' Then 3 When
c.subjectcategory = 'V' Then 4 When c.subjectcategory = 'J' Then 5 End,
c.ancillary, Case When Substring(a.subjectcode, 6, 1) = 'C' Then 1
When Substring(a.subjectcode, 6, 1) = 'S' Then 2 When
Substring(a.subjectcode, 6, 1) = 'A' Then 3 When
Substring(a.subjectcode, 6, 1) = 'E' Then 4 Else 5 End, a.subjectcode
Explain Analyze of DB 1 (client_db) :"Sort (cost=2862.35..2862.36 rows=1
width=1088) (actual time=451671.464..451671.495 rows=326 loops=1)"" Sort Key:
(concat(rtrim((f.degreeawarded)::text), ' ', rtrim((d.departmentname)::text))),
Re: Slow performance
Hi Again,
I have not gone through your explain plans for both, will it be possible
for you to take diff of the both plans. It will give some insight about how
things are going and where the problem is.
Alternatively
I will suggest Reindex and execute ANALYZE command to regenerate stats .
Regards,
Ikram
On Fri, Jul 26, 2024 at 11:05 AM sivapostg...@yahoo.com <
sivapostg...@yahoo.com> wrote:
> I've only one instance of PG in that server. Means only one
> postgresql.conf for both databases.
>
>
> On Friday, 26 July, 2024 at 11:12:34 am IST, Muhammad Ikram <
> mmik...@gmail.com> wrote:
>
>
> Hi,
>
> Could you perform diff on postgresql.conf file to see whether values are
> same for work_mem, shared_buffers, maintenance_work_mem and other related
> parameters?
>
> Regards,
> Ikram
>
>
> On Fri, Jul 26, 2024 at 10:31 AM sivapostg...@yahoo.com <
> sivapostg...@yahoo.com> wrote:
>
> Hello,
> Using PG 11.11, One PG Cluster, Windows 2019 Server Standard, Two
> databases with identical data.
>
> 1. First DB: client_db
> 2. Second DB: client_test
>
> Took backup (pg_dump) of first database (client_db) and restored the
> database as second database (client_test).
>
> Query:
> Select a.examname, a.registrationnumber, b.studentname, d.departmentname,
> e.levelname,
> a.subjectcode, c.subjectname, b.regular, a.semester,
> a.dummynumber, p.semester as curr_sem,
> a.internalmark, a.externalmark, a.result, coalesce((a.internalmark
> + a.externalmark),0) as total,
> a.absent, a.malpractice, c.maxinternalmark, f.noofsemester,
> a.examstudentstatus,
> Case When a.result = 'P' Then 'P' Else
> Case When a.result = 'F' and a.malpractice = 'Y' and
> a.examstudentstatus is null Then 'M' Else
> Case When a.result = 'F' and a.absent = 'Y' and
> a.examstudentstatus = 'R' Then 'R.C' Else
> Case When a.result = 'F' and a.absent = 'Y' and
> a.examstudentstatus = 'S' Then 'S.L' Else
> Case When a.result = 'F' and a.absent = 'Y' and
> a.examstudentstatus = 'F' Then 'N.P' Else
> Case When a.result = 'F' and a.absent = 'Y' and
> a.examstudentstatus = 'W' Then 'W.H' Else
> 'RA' End End End End
> End End as res,
> Concat(RTrim(f.degreeawarded), ' ', RTrim(d.departmentname)) as
> course,
> a.revaluation, m.absent as int_abs, n.companyname,
> n.companydescription,
> m.totalmark as int_mark, q.addressone, q.addresstwo,
> Case When a.semester > f.noofsemester Then 'PRIVATE'
> When a.semester <= f.noofsemester and a.semester in (1,2)
> Then 'I - Year'
> When a.semester <= f.noofsemester and a.semester in (3,4)
> Then 'II - Year'
> When a.semester <= f.noofsemester and a.semester in (5,6)
> Then 'III - Year' End as studyr,
>c.subjectserialno, p.regulararrear
> From cl_student_exam_subject a
> Join cl_student_name b
> Onb.companycode = a.companycode
> And b.registrationnumber = a.registrationnumber
> Join cl_subject c
> Onc.companycode = a.companycode
> And c.subjectcode= a.subjectcode
> Join cl_department_header d
> On d.departmentheaderpk = b.departmentheaderfk
> Join cl_level e
> On e.levelpk = b.Levelfk
> Join cl_department_detail f
> On f.departmentheaderfk = b.departmentheaderfk
> And f.levelfk = b.levelfk
> Left Outer Join cl_student_internal_mark m
> Onm.companycode = a.companycode
> And m.registrationnumber = a.registrationnumber
> And m.subjectcode = a.subjectcode
> And m.departmentheaderfk = b.departmentheaderfk
> And m.levelfk = b.levelfk
> And m.Regular = b.Regular
> Join co_company n
> Onn.companycode = a.companycode
> Join cl_student_semester_subject p
> Onp.companycode = a.companycode
> And p.examheaderfk = a.examheaderfk
> And p.subjectcode = a.subjectcode
> And p.registrationnumber = a.registrationnumber
> And p.semester = a.semester
> Join co_company_branch q
> Onn.companycode = a.companycode
> Where a.companycode = '100'
> Anda.examheaderfk = 'BA80952CFF8F4E1C3F9F44B62ED9BF37'
> And (a.examstudentstatus is null or a.examstudentstatus in ('R', 'S',
> 'W'))
> And b.departmentheaderfk in ('04DF8BD89D0844DD4D8AA151EFB28657')
> Andb.levelfk in ('37A9BEC2638844FFD5B1422D83E70EF3')
> Andb.status = 'A'
> Order By Concat(RTrim(f.degreeawarded), ' ', RTrim(d.departmentname)),
> Case When a.semester > f.noofsemester Then 'PRIVATE'
>When a.semester <= f.noofsemester and a.semester in (1,2)
> Then 'I - Year'
>When a.semester <= f.noofsemester and a.semester in (3,4)
> Then 'II - Year'
>When a.semester <= f.noofsemester and a.semester in (5,6)
> Then 'III - Year' End,
> a.registrationnumber, b.regular, p.semester desc,
> c.subjectserialno,
> Case When c.subjectcategory = 'T' Then 1
> When c.subjectcategory = 'P' Then 2
> When c.subjec
Re: PgbackRest PointTIme Recovery : server unable to start back
here the log outputs When I ran $sudo -u enterprisedb pgbackrest --stanza=Demo_Repo --delta --set=20240719-122703F_20240724-094727I --target-timeline=current --type=time "--target=2024-07-24 09:44:01.3255+05:30" --target-action=promote restore The Edb server log out put shows ... 2024-07-23 15:34:12 IST ERROR: syntax error at or near "selecct" at character 1 2024-07-23 15:34:12 IST STATEMENT: selecct current_timestamp 2024-07-23 15:34:26 IST FATAL: database "enterprisedb" does not exist 2024-07-24 09:38:20 IST FATAL: database "enterprisedb" does not exist 2024-07-24 09:42:17 IST FATAL: database "test1" does not exist 2024-07-24 09:43:56 IST FATAL: database "enterprisedb" does not exist 2024-07-24 09:45:07 IST FATAL: database "enterprisedb" does not exist 2024-07-24 09:45:16 IST ERROR: relation "important_table" does not exist at character 58 2024-07-24 09:45:16 IST STATEMENT: begin; drop table important_table; commit; select * from important_table; 2024-07-24 09:46:00 IST LOG: checkpoint starting: time 2024-07-24 09:46:02 IST LOG: checkpoint complete: wrote 16 buffers (0.1%); 0 WAL file(s) added, 0 removed, 0 recycled; write=1.506 s, sync=0.001 s, total=1.509 s; sync files=9, longest=0.001 s, average=0.001 s; distance=93 kB, estimate=171 kB; lsn=0/3C045768, redo lsn=0/3C045730 2024-07-24 09:47:27 IST LOG: checkpoint starting: immediate force wait 2024-07-24 09:47:27 IST LOG: checkpoint complete: wrote 2 buffers (0.0%); 0 WAL file(s) added, 0 removed, 1 recycled; write=0.001 s, sync=0.001 s, total=0.003 s; sync files=1, longest=0.001 s, average=0.001 s; distance=16106 kB, estimate=16106 kB; lsn=0/3D60, redo lsn=0/3D28 2024-07-24 09:47:27.788 P00 INFO: archive-push command begin 2.52.1: [pg_wal/0009003C] --exec-id=34036-c1d6de0b --log-level-console=info --log-level-file=debug --pg1-path=/var/lib/edb/as16/data --pg-version-force=16 --repo1-host=10.10.20.7 --repo1-host-user=postgres --stanza=Demo_Repo 2024-07-24 09:47:28.130 P00 INFO: pushed WAL file '0009003C' to the archive 2024-07-24 09:47:28.230 P00 INFO: archive-push command end: completed successfully (444ms) When I ran $ sudo systemctl start edb-as-16.service 4-07-26 11:32:56 IST LOG: starting PostgreSQL 16.3 (EnterpriseDB Advanced Server 16.3.0) on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.4.1 20231218 (Red Hat 11.4.1-3), 64-bit 2024-07-26 11:32:56 IST LOG: listening on IPv4 address "0.0.0.0", port 5444 2024-07-26 11:32:56 IST LOG: listening on IPv6 address "::", port 5444 2024-07-26 11:32:56 IST LOG: listening on Unix socket "/tmp/.s.PGSQL.5444" 2024-07-26 11:32:56 IST LOG: database system was interrupted; last known up at 2024-07-24 09:47:27 IST 2024-07-26 11:32:56 IST LOG: starting point-in-time recovery to 2024-07-24 09:44:01.3255+05:30 2024-07-26 11:32:56 IST LOG: starting backup recovery with redo LSN 0/3D28, checkpoint LSN 0/3D60, on timeline ID 9 2024-07-26 11:32:56.475 P00 INFO: archive-get command begin 2.52.1: [0009.history, pg_wal/RECOVERYHISTORY] --exec-id=43293-1d583a8e --log-level-console=info --log-level-file=debug --pg1-path=/var/lib/edb/as16/data --pg-version-force=16 --repo1-host=10.10.20.7 --repo1-host-user=postgres --stanza=Demo_Repo 2024-07-26 11:32:56.709 P00 INFO: found 0009.history in the repo1: 16-1 archive 2024-07-26 11:32:56.809 P00 INFO: archive-get command end: completed successfully (335ms) 2024-07-26 11:32:56 IST LOG: restored log file "0009.history" from archive 2024-07-26 11:32:56.816 P00 INFO: archive-get command begin 2.52.1: [0009003D, pg_wal/RECOVERYXLOG] --exec-id=43295-55c5d6f7 --log-level-console=info --log-level-file=debug --pg1-path=/var/lib/edb/as16/data --pg-version-force=16 --repo1-host=10.10.20.7 --repo1-host-user=postgres --stanza=Demo_Repo 2024-07-26 11:32:56.999 P00 INFO: found 0009003D in the repo1: 16-1 archive 2024-07-26 11:32:57.099 P00 INFO: archive-get command end: completed successfully (285ms) 2024-07-26 11:32:57 IST LOG: restored log file "0009003D" from archive 2024-07-26 11:32:57 IST LOG: redo starts at 0/3D28 2024-07-26 11:32:57.114 P00 INFO: archive-get command begin 2.52.1: [0009003E, pg_wal/RECOVERYXLOG] --exec-id=43297-34867c05 --log-level-console=info --log-level-file=debug --pg1-path=/var/lib/edb/as16/data --pg-version-force=16 --repo1-host=10.10.20.7 --repo1-host-user=postgres --stanza=Demo_Repo 2024-07-26 11:32:57.282 P00 INFO: found 0009003E in the repo1: 16-1 archive 2024-07-26 11:32:57.382 P00 INFO: archive-get command end: completed successfully (270ms) 2024-07-26 11:32:57 IST LOG: restored log file "0009003E" from archive 2024-07-26 11:32:57.400 P00 INFO: archive-get command begin 2.52.1: [00090
Re: PgbackRest PointTIme Recovery : server unable to start back
I don't think it has anything to do with the pg_hba.conf file. Archive seems missing. I will suggest taking a full backup, as you have mentioned that you are able to take full backup. Regards, Ikram On Fri, Jul 26, 2024 at 11:24 AM KK CHN wrote: > here the log outputs > > When I ran > > $sudo -u enterprisedb pgbackrest --stanza=Demo_Repo --delta > --set=20240719-122703F_20240724-094727I --target-timeline=current > --type=time "--target=2024-07-24 09:44:01.3255+05:30" > --target-action=promote restore > > > > The Edb server log out put shows > ... > > 2024-07-23 15:34:12 IST ERROR: syntax error at or near "selecct" at > character 1 > 2024-07-23 15:34:12 IST STATEMENT: selecct current_timestamp > 2024-07-23 15:34:26 IST FATAL: database "enterprisedb" does not exist > 2024-07-24 09:38:20 IST FATAL: database "enterprisedb" does not exist > 2024-07-24 09:42:17 IST FATAL: database "test1" does not exist > 2024-07-24 09:43:56 IST FATAL: database "enterprisedb" does not exist > 2024-07-24 09:45:07 IST FATAL: database "enterprisedb" does not exist > 2024-07-24 09:45:16 IST ERROR: relation "important_table" does not exist > at character 58 > 2024-07-24 09:45:16 IST STATEMENT: begin; drop table important_table; > commit; select * from important_table; > 2024-07-24 09:46:00 IST LOG: checkpoint starting: time > 2024-07-24 09:46:02 IST LOG: checkpoint complete: wrote 16 buffers > (0.1%); 0 WAL file(s) added, 0 removed, 0 recycled; write=1.506 s, > sync=0.001 s, total=1.509 s; sync files=9, longest=0.001 s, average=0.001 > s; distance=93 kB, estimate=171 kB; lsn=0/3C045768, redo lsn=0/3C045730 > 2024-07-24 09:47:27 IST LOG: checkpoint starting: immediate force wait > 2024-07-24 09:47:27 IST LOG: checkpoint complete: wrote 2 buffers (0.0%); > 0 WAL file(s) added, 0 removed, 1 recycled; write=0.001 s, sync=0.001 s, > total=0.003 s; sync files=1, longest=0.001 s, average=0.001 s; > distance=16106 kB, estimate=16106 kB; lsn=0/3D60, redo lsn=0/3D28 > 2024-07-24 09:47:27.788 P00 INFO: archive-push command begin 2.52.1: > [pg_wal/0009003C] --exec-id=34036-c1d6de0b > --log-level-console=info --log-level-file=debug > --pg1-path=/var/lib/edb/as16/data --pg-version-force=16 > --repo1-host=10.10.20.7 --repo1-host-user=postgres --stanza=Demo_Repo > 2024-07-24 09:47:28.130 P00 INFO: pushed WAL file > '0009003C' to the archive > 2024-07-24 09:47:28.230 P00 INFO: archive-push command end: completed > successfully (444ms) > > > > When I ran $ sudo systemctl start edb-as-16.service > > 4-07-26 11:32:56 IST LOG: starting PostgreSQL 16.3 (EnterpriseDB Advanced > Server 16.3.0) on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.4.1 > 20231218 (Red Hat 11.4.1-3), 64-bit > 2024-07-26 11:32:56 IST LOG: listening on IPv4 address "0.0.0.0", port > 5444 > 2024-07-26 11:32:56 IST LOG: listening on IPv6 address "::", port 5444 > 2024-07-26 11:32:56 IST LOG: listening on Unix socket "/tmp/.s.PGSQL.5444" > 2024-07-26 11:32:56 IST LOG: database system was interrupted; last known > up at 2024-07-24 09:47:27 IST > 2024-07-26 11:32:56 IST LOG: starting point-in-time recovery to > 2024-07-24 09:44:01.3255+05:30 > 2024-07-26 11:32:56 IST LOG: starting backup recovery with redo LSN > 0/3D28, checkpoint LSN 0/3D60, on timeline ID 9 > 2024-07-26 11:32:56.475 P00 INFO: archive-get command begin 2.52.1: > [0009.history, pg_wal/RECOVERYHISTORY] --exec-id=43293-1d583a8e > --log-level-console=info --log-level-file=debug > --pg1-path=/var/lib/edb/as16/data --pg-version-force=16 > --repo1-host=10.10.20.7 --repo1-host-user=postgres --stanza=Demo_Repo > 2024-07-26 11:32:56.709 P00 INFO: found 0009.history in the repo1: > 16-1 archive > 2024-07-26 11:32:56.809 P00 INFO: archive-get command end: completed > successfully (335ms) > 2024-07-26 11:32:56 IST LOG: restored log file "0009.history" from > archive > 2024-07-26 11:32:56.816 P00 INFO: archive-get command begin 2.52.1: > [0009003D, pg_wal/RECOVERYXLOG] --exec-id=43295-55c5d6f7 > --log-level-console=info --log-level-file=debug > --pg1-path=/var/lib/edb/as16/data --pg-version-force=16 > --repo1-host=10.10.20.7 --repo1-host-user=postgres --stanza=Demo_Repo > 2024-07-26 11:32:56.999 P00 INFO: found 0009003D in the > repo1: 16-1 archive > 2024-07-26 11:32:57.099 P00 INFO: archive-get command end: completed > successfully (285ms) > 2024-07-26 11:32:57 IST LOG: restored log file "0009003D" > from archive > 2024-07-26 11:32:57 IST LOG: redo starts at 0/3D28 > 2024-07-26 11:32:57.114 P00 INFO: archive-get command begin 2.52.1: > [0009003E, pg_wal/RECOVERYXLOG] --exec-id=43297-34867c05 > --log-level-console=info --log-level-file=debug > --pg1-path=/var/lib/edb/as16/data --pg-version-force=16 > --repo1-hos
