Hi, I am having issues restoring data

Hi, I am having issues restoring data from an sql using psql on a postgres enabled database the error i am getting is ERROR: role "postgres" does not exist
9 Replies
cmck
cmck3w ago
Hi thanks for reaching out, llikely this is a permissions conflict between the content of the sql file you're restoring and the set permissions granted to users in xata. Does the sql file contain any CREATE USER statements?
Kaligraphy
KaligraphyOP3w ago
Hello, thanks for reaching out: it has statements like this : ALTER SCHEMA database_name OWNER TO postgres; and this: ALTER TABLE database_name.bip_book_versions OWNER TO postgres
cmck
cmck3w ago
So changing the ownership of databases and tables in xata is not supported. See https://xata.io/docs/postgres#import The error you're getting is due to the fact that the "postgres" role doesn't exist in xata and it can't be created (for security reasons). I would try commenting out these lines in the sql import if you can and see if the import works.
Connect to Postgres
Learn how to connect to Postgres directly via the wire protocol
Kaligraphy
KaligraphyOP3w ago
from what i can see, there xataadmin role, i also tried that but it does not work, the last resort would be remove all ownership command The import was successful after after commenting out the lines, but I cannot see the result in the UI or via psql however, select * from information_schema.tables where table_schema = 'table_name'; shows the schema, and tables. also trying to get data from that ay tables indicates that the tables does not exist Is it possible to see the data via Xata UI?
cmck
cmck3w ago
If the import was successful it should be visible in the Xata UI. Can you please enable support access to the database so I can take a look? If you are comfortable with sharing the data, you could also open a support case at [email protected] and attach your sql file there so I can test the import on my end.
Kaligraphy
KaligraphyOP3w ago
yea sure may I send a private message?
cmck
cmck3w ago
If that's easier, sure!
Kaligraphy
KaligraphyOP3w ago
there might be private info, I dont want to leave it in a public thread
cmck
cmck3w ago
Of course, I understand 👍
Want results from more Discord servers?
Add your server