PostgreSQL

15 October 2024 12:26

"all other servers" is not *this* server. Check pg_stat_archiver and the server log (assuming you have log_collector on)

PostgreSQL

15 October 2024 12:10

It seems someone persists to index with fts a lo data type https://github.com/darold/ora2pg/issues/1829

PostgreSQL

15 October 2024 11:27

Yes, archiving working fine on all other servers. I can see the same wal files in both archive path and pg_wal

PostgreSQL

15 October 2024 11:24

Yes, the same thing happening on all other servers.

PostgreSQL

15 October 2024 11:23

Need to remove archive on secondary side?

PostgreSQL

15 October 2024 11:21

Did you copy over the postgresql.conf file and perhaps forgot to remove the archive_command and now it's trying to archive and failing continuously?

PostgreSQL

15 October 2024 09:11

Ah, c'mon! That's a bit weak... if ppl would start reading man pages, this channel would lose ~ half its traffic 🤪

PostgreSQL

15 October 2024 08:12

Good luck importing your logical backup in the middle of an emergency

PostgreSQL

15 October 2024 04:59

I guess he meant it is not a reliable backup that you can rely on, especially if you're having high workload and size on the database

PostgreSQL

14 October 2024 19:47

But pg_dump is not a backup!

PostgreSQL

14 October 2024 16:15

There is no such command.

The standard backup tools take backups of the entire database cluster.

PostgreSQL

14 October 2024 15:30

Hi all, can anyone send me the command to take the back up of a particular schema?

PostgreSQL

14 October 2024 11:30

Well, I guess Jaisankar will have to provide some more details. Or he/she is happy already and will stick to this ovious antipattern... 🤷

PostgreSQL

14 October 2024 11:26

Just use UTF-256 and Lenin will have bigger impact.

PostgreSQL

14 October 2024 10:49

In fact, I doubt there could really be a single TEXT of 1GB size.

The full collected works of Lenin in 55 volumes is a kind of 70MB .doc.
Even considering a very good LZ compression ratio of 80% — there would be less than 350MB of text.
Considering that there are A LOT of images and formatting (tables, etc) — a real extracted text would be kind of 100MB or so.

And yet it is doubteful that this should be put in and database as a whole, not per-volume.


It seems that there just should be no coherent TEXT of that size — and it could not be read and understood by a person anyway.

PostgreSQL

15 October 2024 12:14

It depends on how they implemented the archiving. But yeah, we need more info about the setup.

PostgreSQL

15 October 2024 12:09

The purge of archive files is not automatically, please apply your retention policy to your backup and archive files in both servers

PostgreSQL

15 October 2024 11:25

So you're archiving both on the primary and secondary? Are you 100% sure archiving on the secondary is working well, then? If it's failing, it'll retain wal files forever.

PostgreSQL

15 October 2024 11:23

Do you want to archive the wal on the secondary side?

PostgreSQL

15 October 2024 11:22

Yes, I copied.archiving working well on secondary. But keeps wal on pg_wal.

PostgreSQL

15 October 2024 11:20

Hello,
Im encountering an issue with postgreSQL version 15 setup. The pg_wal directory on the secondary server is getting full, retaining WAL files from the past four months. However, on the primary server its fine, the pg_wal size is only 1.5GB, whereas the secondary has grown to 28GB.
I confirmed that the configuration parameters are identical on both servers, so Im unsure why this is happening only on the secondary.
Could you suggest anything on that?

PostgreSQL

15 October 2024 08:23

While the statement may be a bit exaggerated — it's definitely true for those who can't find schema key in man pg_dump.

PostgreSQL

15 October 2024 07:48

Oh, come on, I'd love @tzirechnoy to defend that hill 😏

PostgreSQL

14 October 2024 19:48

Well, nothing that only exports one schema is a "backup" in the sense that it can be used to recover an entire cluster, so I assume that's not what the OP was looking for. "Backup" has both specific and general meanings. 😊

PostgreSQL

14 October 2024 19:41

pg_dump can limit what it takes a backup of by schema. The option you want to look for is the -n or --schema option: https://www.postgresql.org/docs/current/app-pgdump.html

PostgreSQL

14 October 2024 15:59

Well you can't create an FTS index on an OID column. Since that is simply a number referencing the large object.

I suspect you'll need to split your very large row with in your 'application'.

If the whole table is only 8GiB, and one column is 1GiB yet others are not. It would probably be sensible to ask why one value is significantly bigger than the others.

But it's hard to suggest anything else without knowing what type of data you have in that single value for it to be so large. Bioinformatics?

PostgreSQL

14 October 2024 12:52

Let me explain what we are doing
We are migrating data from Oracle to PostgreSQL using the ora2pg tool. One of the tables on the Oracle side is approximately 8GB and contains BLOB objects. When attempting to import the .SQL file generated by ora2pg into PostgreSQL, I encountered the following error:
ERROR: invalid memory allocation request size 1073741834
I discovered that a single row in the table is larger than 1GB. To address this, we decided to use the lo_import method in ora2pg, which splits the BLOB data into manageable chunks. This approach requires using the OID data type instead of bytea.
We are now able to successfully import the data but facing issue in creating FTS_index on OID data type column (whereas in oracle it's a oracle Text index in blob column)

PostgreSQL

14 October 2024 11:28

But that's not TEXT documents.

And it's generally insuitable to put FTS on images...

PostgreSQL

14 October 2024 11:19

Maybe it's scanned documents including some OCR metadata in text form. Apparently OP ran into the issue, so he *has* documents with > 1 GB 🤷

PostgreSQL

14 October 2024 09:48

Absolutely, but let's just assume that it wasn't Jaisankar who initially designed this system... ;-)