Postgres user "postgres" is not available?

1

I have a SQL dump that’s ~500mb. I’ve been trying to restore it but I get thousands of these errors:

user=example_user,db=example_pg,app=psql,client=10.220.251.243,ERROR: must be member of role "postgres"

This is because the dump file has postgres as the owner of everything. Am I not allowed to login as this user? It’s my database after all…

2

Hi there,

You do not have superuser permission on your Postgres service so will not get access to the postgres role. My guess is when creating the dump file, you exported your entire database when you only needed to export the tables specifically required by your service. Assuming all your tables are in the public schema try using the following command to export your database:

PGPASSWORD={PASSWORD} pg_dump -h <export-host> -U <export-user> <database-to-export> -n public --no-owner > database_dump.sql

Regards,

Keith
Render Support, UTC+10 :australia:

3

This is a multi-tenant app with one schema per workspace. So there’s hundreds of schemas that are needed (one per customer). The --no-owner flag looks helpful though, I’ll try that.

4

I am hitting the same same error must be member of role "postgres" while trying to run Drizzle migrations. Has anyone found a workaround?

5

Hi there,

Can you share the command you are using to create your dump file? Seeing it is a multi-tenant app I guess you use pg_dumpall.

Try using a command such as this:

PGPASSWORD=<export-password> pg_dumpall -h <export-host> -U <export-user> -w --no-owner --clean --no-privileges --if-exists --exclude-database=postgres --exclude-database=template0 --exclude-database=template1 > dump.sql

After that, check the SQL file to see if it is trying to create the postgres or do anything with this user. You can always comment these lines out, the dump file is just SQL.

Regards,

Keith
Render Support, UTC+10 :australia:

6

Unfortunately I don’t have time to dig in the in the Drizzle internals right now to sort it out. (It’s somewhere in their migration utils). I’ll have to move this particular project elsewhere for now. Love Render overall though. It’s at the top of my list for most projects.

7

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.