-
Notifications
You must be signed in to change notification settings - Fork 48
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Can't run on a read-only DB. #31
Comments
Interesting. What pg_sample does is copy a subset of data from each table into temp tables under the "sample_schema". If then continues to recursively add data to them to satisfy any FK constraints. Then the result is written to stdout as a dump under the original table names. How are the permissions set? Is it possible to only allow writing to a specific schema? |
It seems I only have SELECT permissions on this particular DB unfortunately. Do you have any alternative to this by any chance? The script is more or less fantastic for my use case but just have this one hurdle. |
I'm not sure. There are ways to do cross-database queries, apparently. I've never tried any of them: So if we could run all the queries on the read-only but write them to a separate writable database then that might work, but I'm sure would require some changes. Would need to experiment with it. I'll try to read up a bit on the options if you think that's a possible route? |
That seems like an awesome solution! I think it would be really helpful for not only me but anyone else who comes across a similar issue with restrictions. I'm also going to look into this on my end. 👍 Thank you |
Ok, I think I have something kinda sorta working. I created the foreign db connection with postgres_fdw. I connected the "public" remote schema to a local schema. The way the existing code iterates over the tables doesn't bring back the foreign tables, so you need this patch:
In terms of connecting the foreign db, this is what I did (in my case the dbs are running on the same instance):
Note that I imported the schema (but not the data) to the temp database because otherwise I was getting undefined type errors when I tried to do the foreign connection. With that, I'm now able to run pg_sample against the test database. The output however has the foreign connection schema, so it would require some post-processing to get it to match the original db. |
Ah, I tried this out so far and it works for me so far (like you mentioned I had to do some post processing to add data types) Past this point I think it's a matter of modifying the code that's there to select and insert rows into the temporary table into selecting the sample rows but then using a separate connection to insert them into this schema that's been copied over. Not sure what that would take though. |
Hm, just tried this again and I noticed that when I try to run it, it errors out saying something about a view? I'd like to ignore the views
|
@mehulp118 You applied the patch ^^^? Not sure why it would be returning views, since we explicitly ask for WHERE table_type NOT IN ('VIEW') But yes, it seems like it was close to working, except for the naming issue. I'll try to take another look this week. |
Has there been any progress on this? |
Hi,
I'm attempting to run this on a read-only replica of a database and I noticed that this is failing because of the following line.
pg_sample/pg_sample
Line 488 in 27c2916
I noticed here that the pg_sample triggers CREATE SCHEMA. Would you be able to give an explanation as to why I would need write / creating access to complete this task.
Are there any alternatives that would allow me to run this in a read-only db?
The text was updated successfully, but these errors were encountered: