PostgreSQL

27 Sep 2024 11:09

You seem to have a misconception there. If *you* don't define an ID, your record doesn't have any. The tinme where each tuple had an OID are *long* gone...

PostgreSQL

27 Sep 2024 09:49

I repeat: "Which ID?"

PostgreSQL

27 Sep 2024 09:30

Which ID?
If you mean foreign key constraints, those are visible in e.g. the psql \d output:

demo=> create table a (id int primary key);
CREATE TABLE
demo=> create table b (id int, fk_a int references a(id));
CREATE TABLE
demo=> \d a
                 Table "public.a"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 id     | integer |           | not null | 
Indexes:
    "a_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "b" CONSTRAINT "b_fk_a_fkey" FOREIGN KEY (fk_a) REFERENCES a(id)

demo=> 

PostgreSQL

27 Sep 2024 07:33

Also, you can study pg_depend and pg_shdepend relations, this way you can get OIDs of relations owned by role

PostgreSQL

27 Sep 2024 06:35

How to find out list objects to reassign them to other user/role

PostgreSQL

27 Sep 2024 00:15

Hi Experts, Any idea how to drop any role/user in postgresql,  while I am droping role/user I am getting 120 objects are dependent on <dbname> and in another db it is showing many privileges of table are dependent. I tried with few queries to identify from internet object/grant list which are dependent on that role/user. But no luck...

PostgreSQL

26 Sep 2024 18:55

This is not the place. But Duo Lingo is a free phone app. I am using it to learn Russian.

PostgreSQL

26 Sep 2024 16:47

I'd like more to implement a database restore in an existing cluster.

PostgreSQL

26 Sep 2024 16:41

Probably easier to implement an AM that uses an undo log instead 😏🤪

PostgreSQL

26 Sep 2024 16:36

PG 17 was released ~ 1.5 hours ago 🍾

PostgreSQL

26 Sep 2024 16:34

Rigth! Well, the frozen bit could be set everywhere ;-)

PostgreSQL

26 Sep 2024 16:34

Yeah that was the issue that came to my mind

PostgreSQL

26 Sep 2024 16:33

No they are not the same

PostgreSQL

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

27 Sep 2024 09:53

Record's id that is used in other tables.

PostgreSQL

27 Sep 2024 09:42

I actually meant finding all tables that reference that specific record by its ID, not just foreign keys. Is there a way to find all the tables where that record is used?

PostgreSQL

27 Sep 2024 09:13

Hi everyone, is there a way in PostgreSQL to find the dependent tables and columns for a record based on its id?

PostgreSQL

27 Sep 2024 07:32

You don't have to. You simply REASSIGN OWNED BY $role TO ?? and then drop role

PostgreSQL

27 Sep 2024 00:22

You can use REASSIGN OWNED to reassign any objects owned by one role to another. It's specifically to allow you to then drop the role you did the reassign on.

PostgreSQL

26 Sep 2024 22:53

Dear team,
I am looking for a comprehensive document or suggestions on best practices for the end-to-end setup of PostgreSQL 16 on a new server, including installation, replication setup, monitoring tool configuration, and backup strategy. If anyone has such documentation or can provide insights, it would be greatly appreciated.
Thank you.

PostgreSQL

26 Sep 2024 18:36

Is there anyone who can help me improve my English speaking skills?

PostgreSQL

26 Sep 2024 16:44

https://www.postgresql.org/about/news/postgresql-17-released-2936/

and here is the full release notes:
https://www.postgresql.org/docs/17/release-17.html

PostgreSQL

26 Sep 2024 16:38

1) It requires to really set that everywhere.
2) I doubt that frozen-deleted isn't well-tested and may involve xid clash. Generally safer would be to rewrite xids completely/

3) I thought to write such an importer... Somwtimes. Some global objects should be checked: users, tablespaces...

PostgreSQL

26 Sep 2024 16:35

Alas! even dicussing this could lead a lot of people coming here to wrong ideas.

*DON'T* try this at home, kids!

PostgreSQL

26 Sep 2024 16:34

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

PostgreSQL

26 Sep 2024 16:34

It shouldn't. Xids are different.

PostgreSQL

26 Sep 2024 16:32

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

PostgreSQL

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

Maybe if you do it in a transaction 🤪