Message-ID: <9b32b21c0b85692f99213a5adb4cf07dd43d3ad3.camel@cybertec.at>
Subject: Re: PGDump question/issue
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Ayden Gera <aydengera@gmail.com>, pgsql-novice@lists.postgresql.org
Date: Fri, 25 Apr 2025 08:15:37 +0200
In-Reply-To: 
 <CANYJdW+Zq=CUbyMLiVqQ3nipS4S=W_Jn6J_bY=49qw1EDbBwyg@mail.gmail.com>
References: 
	<CANYJdW+Zq=CUbyMLiVqQ3nipS4S=W_Jn6J_bY=49qw1EDbBwyg@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.54.3 (3.54.3-1.fc41) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/9b32b21c0b85692f99213a5adb4cf07dd43d3ad3.camel%40cybertec.at>
Precedence: bulk

On Fri, 2025-04-25 at 09:50 +1200, Ayden Gera wrote:
> Hoping someone may have a solution to this problem.
> We get a daily PGDump file (@3Gb) from our SaaS provider (for BI purpose)=
.
> In it, it has a Drop Table IF Exists command..
> This file has no row level security etc.
> We want=C2=A0 to use the same file to populate Supabase with and add row =
level
> security.. but I believe the drop table will destroy the rls each day and
> manually adding it back *unless mabe scripted) isn't an option.
>=20
> We have an inhouse Postgresql we can also use to potentially load and the=
n
> do its own PGDump with data only..
>=20
> But the other issue we have is the source tables don't always have any
> unique keys that we can tell.. so to be safe and avoid data duplicate ris=
k..
> we prefer to delete the entire tables data before inserting..
>=20
> Does anyone have any suggestions on how to best automate the daily updati=
ng
> of data into the supabase tables without losing any RLS we might configur=
e
> on those tables?
> Or what commands should we run on our own PG to get our own data only/ins=
ert
> + commands to drop all data in all tables before running it.
>=20
> I was also wondering if we could send PGDump from SaaS to Supabase Db1 an=
d
> then stream data to DB2 (Prod) but unclear if we can and/or risk data
> duplication risk if we cannot somehow delete the tables in Prod just befo=
re
> streaming..

I am not sure I understand correctly: your problem is that you want to copy
data from a database (let's call it database A) to another database B.
In the process, you want to wipe out all the data in B, but not the table
definitions, because there are different row-level security policies on the
tables in A and B.

Correct?

Then perhaps this will help:

1. export the object definitions from B:

   pg_dump -F c --schema-only -f dumpb B

2. export the data from A:

   pg_dump -F c --data-only -f dumpa A

3. drop database B, create it again and create all the objects:

   pg_restore -d postgres --clean --create --section=3Dpre-data dumpb

4. restore the data from A into the new database:

   pg_restore -d B --section=3Ddata dumpa

5. restore the original index definitions and constraints:

   pg_restore -d B --section=3Dpost-data dumpb

Yours,
Laurenz Albe