Re: AW: [Extern] Re: consistent postgresql snapshot
Talking about fsfreeze and blocksize are not relevant in your case at all. You can't make a backup this way any way. According your mail, you are playing with database recovery after crash. Is pg crash proof? Yes ( https://www.postgresql.org/docs/current/wal-intro.html). You can use this solution for example to make a test environment and it works, but not for live database backup. For backup use a pg_basebackup or pg_start_backup()/snap/pg_stop_backup() solution br Kaido On Thu, 12 May 2022 at 17:53, Nick Cleaton wrote: > On Thu, 12 May 2022 at 14:48, Tom Lane wrote: > >> "Zwettler Markus (OIZ)" writes: >> > I don't want to do use the normal backup algorithm where >> pg_start_backup + pg_stop_backup will fix any fractured block and I am >> required to have all archived logfiles, therefore. >> > I want to produce an atomic consistent disk snapshot. >> >> [ shrug... ] You can't have that. [snip] >> >> The only way you could get a consistent on-disk image is to shut >> the server down (being sure to do a clean not "immediate" shutdown) >> and then take the snapshot. >> > > I think you could work around that by taking a dirty snapshot, making a > writable filesystem from it, waiting until you've archived enough WAL to > get that to a consistent state, and then firing up a temporary postmaster > on that filesystem to go through recovery and shut down cleanly. > >
Re: Link WAL record to session
Hi Take a look at parameter log_line_prefix br Kaido On Tue, 24 Jan 2023, 20:36 Sergey Burladyan, wrote: > > Hi, All! > > Is it possible to associate a WAL record with it session/process id? > > For example, if I have this record in WAL: > rmgr: Standby len (rec/tot): 42/42, tx: 1142213518, lsn: > 36174/FDC19050, prev 36174/FDC19020, desc: LOCK xid 1142213518 db 13091 rel > 733108033 > > Can I find session that writes it? > > -- > Sergey Burladyan > > >
re: Link WAL record to session
xid in wal should be transaction id. It is one option (with pid) for log line prefix br Kaido On Tue, 24 Jan 2023, 21:37 David G. Johnston, wrote: > On Tue, Jan 24, 2023 at 12:30 PM kaido vaikla > wrote: > >> Hi >> Take a look at parameter >> log_line_prefix >> >>> >>> > Really? Care to explain in more detail. I wouldn't expect that setting > to have anything to do with WAL. > > David J. > >
Re: vacuumdb seems not to like option -j when run from crontab
Add a pg binary path to script br Kaido On Mon, 4 Dec 2023 at 18:13, Alan Hodgson wrote: > On Mon, 2023-12-04 at 11:07 -0500, Ron Johnson wrote: > > PG 9.6.24 (Yes, it's EOL.) > > When running "vacuumdb -p5433 -j4 --analyze tap_d" from a bash prompt, it > works as expected: > $ vacuumdb -p5433 -j4 --analyze tap_d > vacuumdb: vacuuming database "tap_d" > > But not when running from crontab: > vacuumdb -p5433 -j4 --analyze tap_d > vacuumdb: invalid option -- 'j' > Try "vacuumdb --help" for more information. > > Obviously I'm missing something, but don't see what it is. Attached is > the script it runs from. > > > Is your user and PATH the same? ie. are you running the same vacuumdb > executable? > >
Re: failed to setup barman backup when Posgres is running in Podman Container
Hi, It's a typical error for brand new setup. After new backup setup do $ barman switch-xlog --force --archive testdb br Kaido On Wed, 10 Jan 2024 at 15:58, duc hiep ha wrote: > Dear All, > > I am trying to use Barman to back up PostgreSQL, which is running in a > Podman container. However, I encounter the error "WAL archive: FAILED," as > shown below: > > #barman check testdb > Server testdb: > WAL archive: FAILED (please make sure WAL shipping is set up) > PostgreSQL: OK > superuser: OK > PostgreSQL streaming: OK > wal_level: OK > replication slot: OK > directories: OK > retention policy settings: OK > backup maximum age: OK (no last_backup_maximum_age provided) > compression settings: OK > failed backups: OK (there are 0 failed backups) > minimum redundancy requirements: OK (have 0 backups, expected at least 0) > pg_basebackup: OK > pg_basebackup compatible: OK > pg_basebackup supports tablespaces mapping: OK > pg_receivexlog: OK > pg_receivexlog compatible: OK > receive-wal running: OK > archiver errors: OK > > I have identified that normally, when the PostgreSQL database is installed > on a regular VM (not in a container), the archive_command parameter under > postgresql.conf looks like this: > > archive_command = 'rsync -a %p barman@barman-backup-server-ip > :/data/barman/main-db-server/incoming/%f' > > However, when PostgreSQL is installed on a Podman container, I cannot use > the above archive_command because within the PostgreSQL container, it > doesn't understand rsync, ssh, and the barman user. That's why I have to > use a normal copy command like: > > archive_command = 'cp -i %p /srv/archive/%f' > > I then mounted this wal-files folder on > '/data/barman/main-db-server/incoming/%f'. As a result, all these wal_files > have the PostgreSQL ownership and not the barman ownership. The barman user > has no privileges to change these wal-files, which is why the WAL archive > is marked as FAILED. > > Do you know how to solve this issue? do I have to install Barman and > PostgreSQL in the same Pod container in order to backup PostgreSQL > container? Or is there another solution to this problem? > > Thank you for your support in advance. > > Best, > Ha >
Re: Performance degradation after upgrading from 9.5 to 14
I'm not sure, does it helps you but read this: https://www.cybertec-postgresql.com/en/b-tree-index-improvements-in-postgresql-v12/ "Since upgrading with pg_upgrade does not change the data files, indexes will still be in version 3 after an upgrade" I reindexed all my database, when did upgrade pg<12 -> pg>=12 if pg_upgrade was a tool. exp-imp for upgrade does not need reindex. br Kaido On Wed, 17 Apr 2024 at 20:39, Marcin Giedz wrote: > how about this: > > jit = off ? > > Marcin > > > On Wed, 17 Apr 2024 at 19:33, Johnathan Tiamoh > wrote: > >> 1) How did you upgrade? pg_dump or pg_upgrade? >> >> I use pg_ugrade with kink option. >> >> 2) Did you run ANALYZE to collect statistics after the upgrade? >> >> >> Yes. I ran vacuumdb-analyze in stages after the upgrade >> >> 3) Did you transfer the configuration, or did you just create a new >> cluster with the default values? >> >> I transfer the configuration >> >> 4) What exactly is slower? Queries? Inserts? >> >> queries >> >> 5) Can you quantify the impact? Is it 2x slower? 100x slower? >> >> it's more than 5 times slower than before. Very high load averages >> >> On Wed, Apr 17, 2024 at 1:25 PM Tomas Vondra < >> tomas.von...@enterprisedb.com> wrote: >> >>> On 4/17/24 19:13, Johnathan Tiamoh wrote: >>> > Hello, >>> > >>> > >>> > I performed an upgrade from postgresql-9.5 to postgresql-14 and the >>> > performance has degraded drastically. >>> > >>> > Please, is they any advice on getting performance back ? >>> > >>> >>> There's very little practical advice we can provide based on this >>> report, because it's missing any useful details. There's a number of >>> things that might have caused this, but we'd have to speculate. >>> >>> For example: >>> >>> 1) How did you upgrade? pg_dump or pg_upgrade? >>> >>> 2) Did you run ANALYZE to collect statistics after the upgrade? >>> >>> 3) Did you transfer the configuration, or did you just create a new >>> cluster with the default values? >>> >>> 4) What exactly is slower? Queries? Inserts? >>> >>> 5) Can you quantify the impact? Is it 2x slower? 100x slower? >>> >>> >>> regards >>> >>> >>> -- >>> Tomas Vondra >>> EnterpriseDB: http://www.enterprisedb.com >>> The Enterprise PostgreSQL Company >>> >>
