PostgreSQL

26 September 2024 16:34

The wal would be all wrong as well as the xid cutoffs

PostgreSQL

26 September 2024 16:34

It shouldn't. Xids are different.

PostgreSQL

26 September 2024 16:32

Hmmm, if the OIDs are the same, that _should_ work. Did you also copy the _vm and _fsm files?

PostgreSQL

26 September 2024 12:31

Two clusters, exact same schema, exact same binaries and platform

How come there is not a way to just move the data from one to the other without pg_dump?

PostgreSQL

26 September 2024 11:23

Maybe if you do it in a transaction 🤪

PostgreSQL

26 September 2024 11:10

It's just a matter of using pg_dump with the pattern+schema option and editing the sql manually :(

PostgreSQL

26 September 2024 09:56

0. Backup
1. Create new table with this column
2. Copy data from old table to new one
3. Rename old table
4. Rename new table
5. Check system stability
6. Drop old table

PostgreSQL

26 September 2024 09:53

You can't. Insert the new column at position #1 in your SELECTs. You don't SELECT *, do you? ;-)

PostgreSQL

26 September 2024 09:48

How to add column in first position

PostgreSQL

26 September 2024 08:18

I'd fancy if you'd not post obviously ChatGPT-generated generic stuff _after_ the discussion has moved on already. The problem is RDS related, so generic information is useless.
Actually, I'd fancy if nobody here posts ChatGPT stuff, unless it's something like "How do I teach my coworkers/customers to ask their trivial questions in ChatGPT so I can deal with the non-obvious things?". 🤓

PostgreSQL

25 September 2024 13:00

Yes that's working but customer want old user to work

PostgreSQL

25 September 2024 09:50

Grant rds_replication to <username>;

PostgreSQL

25 September 2024 09:00

This is the error I am getting in our replication tool

PostgreSQL

25 September 2024 07:26

What’s the full error ?

PostgreSQL

25 September 2024 03:31

Yeah, Oracle is very lax at validating the data. We found chr(0) values and others in strings.
Then you start stripping them out, and you get DUPE KEY Violations. LOL
enjoy.

Also, if you dump the data then import it, watch the CODEPAGE "CHCP" (it's best if you export and import while using the same codepage).

PostgreSQL

26 September 2024 16:34

Yeah that was the issue that came to my mind

PostgreSQL

26 September 2024 16:33

No they are not the same

PostgreSQL

26 September 2024 12:31

Ideally it should be a matter of moving the files following the OID of the table and related matters, but I wasn't able to make it work

PostgreSQL

26 September 2024 11:23

I know for a fact that there are patches in the work that will actually allow us to do that. But the current state of the catalog just doesn't cut it.

PostgreSQL

26 September 2024 11:22

Any of these approaches don't "add new column in first position in existing table" 🤷

PostgreSQL

26 September 2024 10:46

I thought that was an answer to "what skills needed to be a middle DBA"...

(Yeah, may be he can't — however that's rather easy.
Either create table as select .... ; recreate indexes/fks/trigges... rename if you can wait some downtime or do essentially the same with the logical replicas....)

PostgreSQL

26 September 2024 09:54

Need to add new column in first position in existing table

PostgreSQL

26 September 2024 09:52

Install pg on several platforms, make routine backups, be able to make replicas and PITR backups, basic linux administration skills, ability to find and reconfigure postgres logs, understanding of autovacuum statistics, xid and wal horizons, and explain output.

PostgreSQL

26 September 2024 09:34

Hi! Can you please tell me what you need to know so that you can be called a postgresql middle dba

PostgreSQL

26 September 2024 07:02

It seems that after upgrading to PostgreSQL 16.2, the replication user is encountering permission issues when trying to access replication slots. This is likely due to role changes or new permission checks introduced in the newer version. Here’s how you can troubleshoot and potentially resolve the issue:
1. Check Replication Role: Ensure that the replication user still has the REPLICATION attribute. Run the following query:
SELECT rolname, rolreplication FROM pg_roles WHERE rolname = 'your_replication_user';

If the rolreplication column is false, you will need to grant the REPLICATION attribute: ALTER ROLE your_replication_user WITH REPLICATION;

2. Check Superuser Status: In PostgreSQL 16, additional privileges or superuser status might be required for certain operations involving replication slots. You can check if the replication user has superuser privileges:

SELECT rolname, rolsuper FROM pg_roles WHERE rolname = 'your_replication_user';

If not, you can grant superuser privileges temporarily for testing:
ALTER ROLE your_replication_user WITH SUPERUSER;

3. Check Replication Slot Permissions: PostgreSQL may require explicit permission to access or create logical replication slots. You can try re-creating the replication slot or granting necessary permissions:

GRANT USAGE ON ALL SLOTS TO your_replication_user;

PostgreSQL

25 September 2024 11:11

Did you try to drop and create that particular replication user ?

PostgreSQL

25 September 2024 09:49

I see that you have hosted your database on rds

So please try to grant the rds_replication access to your replication user

PostgreSQL

25 September 2024 09:00

Only roles with the REPLICATION attribute or privileges of the "rds_replication" role may use replication slots]. Last query [select \
cast(lsn as varchar(17))from pg_create_logical_replication_slot

PostgreSQL

25 September 2024 05:02

I am having one postgres database which was upgraded from 14.1 to 16.2 so now the issue is this was having one Replication user which was working fine but after upgradation it's giving error of permission to replication slot but all permissions are there

PostgreSQL

25 September 2024 03:27

I can only think of a FAILED transaction or a rollback causing this.