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 exist9 Replies
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?
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 postgresSo 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
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?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.
yea sure
may I send a private message?
If that's easier, sure!
there might be private info, I dont want to leave it in a public thread
Of course, I understand 👍