Re: ZFS filesystem - supported ?
On 10/25/2021 10:13 AM, Stephen Frost wrote: Greetings, * Mladen Gogala (gogala.mla...@gmail.com) wrote: On 10/23/21 23:12, Lucas wrote: This has proven to work very well for me. I had to restore a few backups already and it always worked. The bad part is that I need to stop the database before performing the Snapshot, for data integrity, so that means that I have a hot-standby server only for these snapshots. Lucas Actually, you don't need to stop the database. You need to execute pg_start_backup() before taking a snapshot and then pg_stop_backup() when the snapshot is done. You will need to recover the database when you finish the restore but you will not lose any data. I know that pg_begin_backup() and pg_stop_backup() are deprecated but since PostgreSQL doesn't have any API for storage or file system snapshots, that's the only thing that can help you use storage snapshots as backups. To my knowledge,the only database that does have API for storage snapshots is DB2. The API is called "Advanced Copy Services" or ACS. It's documented here: https://www.ibm.com/docs/en/db2/11.1?topic=recovery-db2-advanced-copy-services-acs For Postgres, the old begin/stop backup functions should be sufficient. No, it's not- you must also be sure to archive any WAL that's generated between the pg_start_backup and pg_stop_backup and then to be sure and add into the snapshot the appropriate signal files or recovery.conf, depending on PG version, to indicate that you're restoring from a backup and make sure that the WAL is made available via restore_command. Just doing stat/stop backup is *not* enough and you run the risk of having an invalid backup or corruption when you restore. If the entire system is on a single volume then you could possibly just take a snapshot of it (without any start/stop backup stuff) but it's very risky to do that and then try to do PITR with it because we don't know where consistency is reached in such a case (we *must* play all the way through to the end of the WAL which existed at the time of the snapshot in order to reach consistency). In the end though, really, it's much, much, much better to use a proper backup and archiving tool that's written specifically for PG than to try and roll your own, using snapshots or not. Thanks, Stephen what about BTRFS since it's the successor of ZFS? -- E-BLOKOS
Re: ZFS filesystem - supported ?
On 10/26/2021 2:35 AM, Laura Smith wrote: Sent with ProtonMail Secure Email. ‐‐‐ Original Message ‐‐‐ On Tuesday, October 26th, 2021 at 01:18, Benedict Holland wrote: In my opinion, ext4 will solve any and all problems without a very deep understanding of file system architecture. In short, i would stick with ext4 unless you have a good reason not to. Maybe there is one. I have done this a long time and never thought twice about which file system should support my servers. Curious, when it comes to "traditional" filesystems, why ext4 and not xfs ? AFAIK the legacy issues associated with xfs are long gone ? XFS is indeed for me the most stable and performant for postgresql today. EXT4 was good too, but less performant. -- E-BLOKOS
Re: ZFS filesystem - supported ?
On 10/26/2021 4:42 PM, Mladen Gogala wrote: On 10/26/21 05:35, Laura Smith wrote: Curious, when it comes to "traditional" filesystems, why ext4 and not xfs ? AFAIK the legacy issues associated with xfs are long gone ? XFS is not being very actively developed any more. Ext4 is being actively developed and it has some features to help with SSD space allocation. Phoronix has some very useful benchmarks: https://www.phoronix.com/scan.php?page=news_item&px=Linux-5.14-File-Systems Ext4 is much better than XFS with SQLite tests and almost equal with MariaDB test. PostgreSQL is a relational database (let's forget the object part for now) and the IO patterns will be similar to SQLite and MariaDB. That benchmark is brand new, done on the kernel 5.14. Of course, the only guarantee is doing your own benchmark, with your own application. RedHat and Oracle are mostly maintaining XFS updates, and I didn't see anything saying it's not mainained actively, especially when they offering many solutions with XFS as default -- E-BLOKOS
Cannot pg_dump_all anymore...
I got this error:
pg_dump: error: query returned 0 rows instead of one: EXECUTE
dumpBaseType('794978')
any clue to solve it?
thanks
David
Re: Cannot pg_dump_all anymore...
On 3/18/2025 5:49 AM, Greg Sabino Mullane wrote: First figure out which database is having that issue, by using pg_dump --schema-only on each database in turn. Then run this SQL on the database giving the error to see if the type exists, or what is nearby: select oid, typname, typtype, typnamespace::regnamespace from pg_type where oid <= 794978 order by 1 desc limit 3; Also let us know the version of pg_dump and the version of Postgres being dumped. Cheers, Greg select oid, typname, typtype, typnamespace::regnamespace from pg_type where oid <= 794978 order by 1 desc limit 3; oid | typname | typtype | typnamespace ++-+-- 794970 | log_17167 | c | repack 794969 | _log_17167 | b | repack 794966 | pk_17167 | c | repack (3 rows) Arggh it's repack 🙁 I have no clue how to repair repack -- E-BLOKOS
Re: Cannot pg_dump_all anymore...
On 3/18/2025 3:59 AM, Ron Johnson wrote:
On Tue, Mar 18, 2025 at 5:14 AM E-BLOKOS wrote:
I got this error:
pg_dump: error: query returned 0 rows instead of one: EXECUTE
dumpBaseType('794978')
any clue to solve it?
PG version?
PG 17.4
Whole command line, including all error messages?
setpriv su - postgres -c "pg_dumpall --no-comments -h /run/postgresql -p
5432 > out.sql"
--
E-BLOKOS
Re: Cannot pg_dump_all anymore...
On 3/18/2025 5:49 AM, Greg Sabino Mullane wrote: First figure out which database is having that issue, by using pg_dump --schema-only on each database in turn. Then run this SQL on the database giving the error to see if the type exists, or what is nearby: select oid, typname, typtype, typnamespace::regnamespace from pg_type where oid <= 794978 order by 1 desc limit 3; Also let us know the version of pg_dump and the version of Postgres being dumped. Cheers, Greg -- Crunchy Data - https://www.crunchydata.com Enterprise Postgres Software Products & Tech Support ok I fixed it with: SELECT * FROM pg_depend WHERE objid IN (794964, 794968); DELETE FROM pg_depend WHERE objid IN (794964, 794968); systemctl restart postgresql is it possible a crash happened with a VACUUM and a machine reboot in same time? -- E-BLOKOS
Re: Cannot pg_dump_all anymore...
Hi, On 3/19/2025 7:08 AM, Greg Sabino Mullane wrote: On Wed, Mar 19, 2025 at 10:02 AM E-BLOKOS wrote: is it possible a crash happened with a VACUUM and a machine reboot in same time? More likely to be a problem with pg_repack. Please tell us the exact versions of pg_repack and Postgres in use here. PG 17.4 pg_repack last git thanks David -- E-BLOKOS
