public inbox for [email protected]
help / color / mirror / Atom feedPGDump question/issue
5+ messages / 3 participants
[nested] [flat]
* PGDump question/issue
@ 2025-04-24 21:50 Ayden Gera <[email protected]>
2025-04-25 06:15 ` Re: PGDump question/issue Laurenz Albe <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Ayden Gera @ 2025-04-24 21:50 UTC (permalink / raw)
To: [email protected]
Hi,
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 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.
We have an inhouse Postgresql we can also use to potentially load and then
do its own PGDump with data only..
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
risk.. we prefer to delete the entire tables data before inserting..
Does anyone have any suggestions on how to best automate the daily updating
of data into the supabase tables without losing any RLS we might configure
on those tables?
Or what commands should we run on our own PG to get our own data
only/insert + commands to drop all data in all tables before running it.
I was also wondering if we could send PGDump from SaaS to Supabase Db1 and
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 before
streaming..
Thanks in advance!
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: PGDump question/issue
2025-04-24 21:50 PGDump question/issue Ayden Gera <[email protected]>
@ 2025-04-25 06:15 ` Laurenz Albe <[email protected]>
2025-04-26 10:19 ` Re: PGDump question/issue Ayden Gera <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Laurenz Albe @ 2025-04-25 06:15 UTC (permalink / raw)
To: Ayden Gera <[email protected]>; [email protected]
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 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.
>
> We have an inhouse Postgresql we can also use to potentially load and then
> do its own PGDump with data only..
>
> 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 risk..
> we prefer to delete the entire tables data before inserting..
>
> Does anyone have any suggestions on how to best automate the daily updating
> of data into the supabase tables without losing any RLS we might configure
> on those tables?
> Or what commands should we run on our own PG to get our own data only/insert
> + commands to drop all data in all tables before running it.
>
> I was also wondering if we could send PGDump from SaaS to Supabase Db1 and
> 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 before
> 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=pre-data dumpb
4. restore the data from A into the new database:
pg_restore -d B --section=data dumpa
5. restore the original index definitions and constraints:
pg_restore -d B --section=post-data dumpb
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: PGDump question/issue
2025-04-24 21:50 PGDump question/issue Ayden Gera <[email protected]>
2025-04-25 06:15 ` Re: PGDump question/issue Laurenz Albe <[email protected]>
@ 2025-04-26 10:19 ` Ayden Gera <[email protected]>
2025-04-26 12:04 ` Re: PGDump question/issue Greg Sabino Mullane <[email protected]>
2025-04-26 20:32 ` Re: PGDump question/issue Laurenz Albe <[email protected]>
0 siblings, 2 replies; 5+ messages in thread
From: Ayden Gera @ 2025-04-26 10:19 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; +Cc: [email protected]
Hi Laurenz,
Thank you very much for your reply.
Your assumption was close but not quite correct.
Database A is SaaS provided PGDump. it comes with Drop tables commands in
it. It has no row level security on it.
We want to daily populate DB A into a new DB B (supabase), but we want to
add row level security to DB B. Issue is the drop table commands in the
PGDump A we believe we will lose any RLS we put in place in DB B?
We could put data into a DB C (our own PG instance), then extract data only
from DB C with PGDump-a, and then insert that into DB B (supabase) but we
need a script to drop all data in DB B before uploading PGDump... What is
command we can use either separately or part of the restore PGDump commands
to first delete all data before restoring data into DB B with PGDump DB A?
Or we could put PGDump A into a separate Supabase Db B2, and then somehow
stream only that data into DB B.. but unsure if we can script dropping all
data automatically in DB B before DB2 streams data into it as data doesn't
always have keys/unique identifiers.. so don't want to risk data
double-ups.. dropping data from all table in DB B seems best.. but unclear
if streaming can accommodate some automated scripting to drop DB B before
it starts..?
Im open to any other ideas on how to get around the drop table that comes
in PG Dump from DB A.
Many Thanks.
Ayden
On Fri, Apr 25, 2025 at 6:15 PM Laurenz Albe <[email protected]>
wrote:
> 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 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.
> >
> > We have an inhouse Postgresql we can also use to potentially load and
> then
> > do its own PGDump with data only..
> >
> > 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
> risk..
> > we prefer to delete the entire tables data before inserting..
> >
> > Does anyone have any suggestions on how to best automate the daily
> updating
> > of data into the supabase tables without losing any RLS we might
> configure
> > on those tables?
> > Or what commands should we run on our own PG to get our own data
> only/insert
> > + commands to drop all data in all tables before running it.
> >
> > I was also wondering if we could send PGDump from SaaS to Supabase Db1
> and
> > 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
> before
> > 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=pre-data dumpb
>
> 4. restore the data from A into the new database:
>
> pg_restore -d B --section=data dumpa
>
> 5. restore the original index definitions and constraints:
>
> pg_restore -d B --section=post-data dumpb
>
> Yours,
> Laurenz Albe
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: PGDump question/issue
2025-04-24 21:50 PGDump question/issue Ayden Gera <[email protected]>
2025-04-25 06:15 ` Re: PGDump question/issue Laurenz Albe <[email protected]>
2025-04-26 10:19 ` Re: PGDump question/issue Ayden Gera <[email protected]>
@ 2025-04-26 12:04 ` Greg Sabino Mullane <[email protected]>
1 sibling, 0 replies; 5+ messages in thread
From: Greg Sabino Mullane @ 2025-04-26 12:04 UTC (permalink / raw)
To: Ayden Gera <[email protected]>; +Cc: Laurenz Albe <[email protected]>; [email protected]
>
> SaaS provided PGDump
Is this a custom dump, or just a bunch of ASCII ? Run "file" against it to
find out.
Im open to any other ideas on how to get around the drop table that comes
> in PG Dump from DB A.
Sounds like these drop tables are a good thing. You asked about removing
all the data. DROP TABLE does that. Further, if the schema sent by the SaaS
ever changes, you would want to know that, so it's in your best interest to
keep it's schema-creation commands around (which has the drop-if-exists as
part of that).
If your dumps are custom, the solution Laurenz provided is a good one.
If it is *only* RLS that needs to be recreated, that can be done. It's
weird you don't have a list somewhere of your current settings, but here's
another workaround to create the list on the fly and apply it:
// Save the existing RLS information to a file:
pg_dump -d mydb --section=post-data | grep -E 'ROW LEVEL SECURITY|CREATE
POLICY' > mydb.rls.sql
// Eyeball mydb.rls.pg to make sure it look sane; that's not a 100%
bulletproof regex
// Import the schema from SaaS
// Re-apply the RLS information:
psql --single-transaction --set=ON_ERROR_STOP=on -f mydb.rls.sql
// Bonus step:
git commit mydb.rls.sql -m "Latest RLS for my database"
--
Cheers,
Greg
--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: PGDump question/issue
2025-04-24 21:50 PGDump question/issue Ayden Gera <[email protected]>
2025-04-25 06:15 ` Re: PGDump question/issue Laurenz Albe <[email protected]>
2025-04-26 10:19 ` Re: PGDump question/issue Ayden Gera <[email protected]>
@ 2025-04-26 20:32 ` Laurenz Albe <[email protected]>
1 sibling, 0 replies; 5+ messages in thread
From: Laurenz Albe @ 2025-04-26 20:32 UTC (permalink / raw)
To: Ayden Gera <[email protected]>; +Cc: [email protected]
On Sat, 2025-04-26 at 22:19 +1200, Ayden Gera wrote:
> Database A is SaaS provided PGDump. it comes with Drop tables commands in it.
You mean you cannot influence how that pg_dump is run? Weird.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2025-04-26 20:32 UTC | newest]
Thread overview: 5+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-04-24 21:50 PGDump question/issue Ayden Gera <[email protected]>
2025-04-25 06:15 ` Laurenz Albe <[email protected]>
2025-04-26 10:19 ` Ayden Gera <[email protected]>
2025-04-26 12:04 ` Greg Sabino Mullane <[email protected]>
2025-04-26 20:32 ` Laurenz Albe <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox