PostgreSQL

26 Sep 2024 11:22

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

PostgreSQL

26 Sep 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 Sep 2024 09:54

Need to add new column in first position in existing table

PostgreSQL

26 Sep 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 Sep 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 Sep 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 Sep 2024 11:11

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

PostgreSQL

25 Sep 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 Sep 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 Sep 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 Sep 2024 03:27

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

PostgreSQL

24 Sep 2024 11:59

and if one upgardes to 15+, then lower bound is 9.2 https://github.com/postgres/postgres/commit/e469f0aaf3c586c8390bd65923f97d4b1683cd9f

PostgreSQL

24 Sep 2024 11:56

And
https://www.endpointdev.com/blog/2022/10/upgrading-postgresql-14-to-15-on-fedora-centos-rocky-alma-linux/
basically list the necessary steps (you'll obvioulsy have to replace 15 with 14 and 14 with 9.x).

PostgreSQL

24 Sep 2024 11:50

And can we also directly upgrade from 9.x to 14.x or should I go for incremental upgrade ?

PostgreSQL

24 Sep 2024 11:45

Which flavour?
And are you using the PGDG packages?

PostgreSQL

26 Sep 2024 11:10

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

PostgreSQL

26 Sep 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 Sep 2024 09:53

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

PostgreSQL

26 Sep 2024 09:48

How to add column in first position

PostgreSQL

26 Sep 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 Sep 2024 13:00

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

PostgreSQL

25 Sep 2024 09:50

Grant rds_replication to <username>;

PostgreSQL

25 Sep 2024 09:00

This is the error I am getting in our replication tool

PostgreSQL

25 Sep 2024 07:26

What’s the full error ?

PostgreSQL

25 Sep 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

24 Sep 2024 21:14

Excuse me someone has installed postgis in postgres 13.12 in S.O open suse 15.4 I do not find repository

PostgreSQL

24 Sep 2024 11:57

Your pgpool should get upgraded through the "dnf/yum update".

PostgreSQL

24 Sep 2024 11:54

https://www.postgresql.org/docs/14/pgupgrade.html
"pg_upgrade supports upgrades from 9.0.X and later to the current major release of PostgreSQL, including snapshot and beta releases."

PostgreSQL

24 Sep 2024 11:46

Rhel, yes sir we are using pgdg packages

PostgreSQL

24 Sep 2024 09:44

Hello All,

I need to complete certification on postgres DBA. Could anyone guide me what certification are available and how can I complete that. Is there any dumps available?