MIME-Version: 1.0
References: 
 <CANYJdW+Zq=CUbyMLiVqQ3nipS4S=W_Jn6J_bY=49qw1EDbBwyg@mail.gmail.com>
 <9b32b21c0b85692f99213a5adb4cf07dd43d3ad3.camel@cybertec.at>
 <CANYJdWKsznOaaka97FRwHgDFJBTJ2YU9g_S-S5xC_rh+T1Vb0Q@mail.gmail.com>
In-Reply-To: 
 <CANYJdWKsznOaaka97FRwHgDFJBTJ2YU9g_S-S5xC_rh+T1Vb0Q@mail.gmail.com>
From: Greg Sabino Mullane <htamfids@gmail.com>
Date: Sat, 26 Apr 2025 08:04:43 -0400
Message-ID: 
 <CAKAnmm+BKpMQXiL2ZdXMMf_xg97o_iY0WigFBe2nQ=yU=q++0w@mail.gmail.com>
Subject: Re: PGDump question/issue
To: Ayden Gera <aydengera@gmail.com>
Cc: Laurenz Albe <laurenz.albe@cybertec.at>, pgsql-novice@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000b2815f0633ad43c2"
Archived-At: 
 <https://www.postgresql.org/message-id/CAKAnmm%2BBKpMQXiL2ZdXMMf_xg97o_iY0WigFBe2nQ%3DyU%3Dq%2B%2B0w%40mail.gmail.com>
Precedence: bulk

--000000000000b2815f0633ad43c2
Content-Type: text/plain; charset="UTF-8"

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

--000000000000b2815f0633ad43c2
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><blockquote class=3D"gmail_quote" style=
=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding=
-left:1ex">SaaS provided PGDump</blockquote><div><br></div><div>Is this a c=
ustom dump, or just a bunch of ASCII ? Run &quot;file&quot; against it to f=
ind out.</div><div><br></div><blockquote class=3D"gmail_quote" style=3D"mar=
gin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1=
ex">=C2=A0Im open to any other ideas on how to get around the drop table th=
at comes in PG Dump from DB A.</blockquote><div><br></div><div>Sounds like =
these=C2=A0drop tables are a good thing. You asked about removing all the d=
ata. DROP TABLE does that. Further, if the schema sent by the SaaS ever=C2=
=A0changes, you would want to know that, so it&#39;s in your best interest =
to keep it&#39;s schema-creation commands around (which has the drop-if-exi=
sts as part of that).</div><div><br></div><div>If your dumps are custom, th=
e solution Laurenz provided is a good one.</div><div><br></div><div>If it i=
s *only* RLS that needs to be recreated, that can be done. It&#39;s weird y=
ou don&#39;t have a list somewhere of your=C2=A0current=C2=A0settings, but =
here&#39;s another workaround to create the list on the fly and apply it:</=
div><div><br></div><div>// Save the existing RLS information to a file:</di=
v><div>pg_dump -d mydb --section=3Dpost-data | grep -E &#39;ROW LEVEL SECUR=
ITY|CREATE POLICY&#39; &gt; mydb.rls.sql</div><div>// Eyeball <a href=3D"ht=
tp://mydb.rls.pg">mydb.rls.pg</a> to make sure it look sane; that&#39;s not=
 a 100% bulletproof regex</div><div><br></div><div>// Import the schema fro=
m SaaS</div><div><br></div><div>// Re-apply the RLS information:</div><div>=
psql --single-transaction --set=3DON_ERROR_STOP=3Don -f mydb.rls.sql</div><=
div><br></div><div>// Bonus step:</div><div>git commit mydb.rls.sql -m &quo=
t;Latest RLS for my database&quot;</div><div><br></div><div><br></div></div=
><span class=3D"gmail_signature_prefix">-- </span><br><div dir=3D"ltr" clas=
s=3D"gmail_signature"><div dir=3D"ltr"><div>Cheers,</div><div>Greg</div><di=
v><br></div><div>--</div><div>Crunchy Data - <a href=3D"https://www.crunchy=
data.com" target=3D"_blank">https://www.crunchydata.com</a></div><div>Enter=
prise Postgres Software Products &amp; Tech Support</div><div><br></div></d=
iv></div></div>

--000000000000b2815f0633ad43c2--