Problem with migrate up and PostgreSQL

Local Setup
1

We’re trying to duplicate the production environment that was mentioned in an article about iDream Interactive on the heroic labs blog.


On digital ocean we have an ubuntu droplet connected to their managed postgresql db service. I can connect to the database cluster using psql. But when I run the migrate up command, I get a response from the db saying the schema is outdated, and to run migrate up. Any ideas on this?
This is the command:
./nakama --database.address “user:password@nakama-db-postgresql-nyc1-55357-do-user-6724142-0.db.ondigitalocean.com:25060/nakama?sslmode=require” migrate up

and this is the response:
{“level”:“info”,“ts”:“2019-11-07T15:18:57.051Z”,“msg”:“Database connections”,“dsns”:[“user:password@nakama-db-postgresql-nyc1-55357-do-user-6724142-0.db.ondigitalocean.com:25060/nakama?sslmode=require”]}
{“level”:“info”,“ts”:“2019-11-07T15:18:57.105Z”,“msg”:“Database information”,“version”:“PostgreSQL 11.5 on x86_64-pc-linux-gnu, compiled by gcc, a f9b82fe63 p 32c22893ba, 64-bit”}
{“level”:“fatal”,“ts”:“2019-11-07T15:18:57.113Z”,“msg”:"DB schema outdated, run nakama migrate up",“migrations”:2,“stacktrace”:“github.com/heroiclabs/nakama/v2/migrate.StartupCheck\n\tgithub.com/heroiclabs/nakama/v2@/migrate/migrate.go:79\nmain.main\n\tgithub.com/heroiclabs/nakama/v2@/main.go:113\nruntime.main\n\truntime/proc.go:203”}

I was able to connect a windows nakama binary to a local postgresql database, and the schema was correctly set up.

2

@oscargoldman What version of Nakama do you use? Did you import an older backup onto the cloud instance for your latest environment?

Let me know what the output of this SQL statement looks like:

SELECT id, applied_at FROM migration_info;
3

this is nakama 2.7.0, and we haven’t imported any data so this is a fresh setup. Running that statement shows:
id | applied_at
----±-----------
(0 rows)

4

what privileges does the user need - could that be an issue?

5

@oscargoldman Try the migrate command before the --database.address argument. So the command should look like: nakama migrate up --database.address .... Argument flags do not care about order, but subsommands are sensitive to it.

6

when I try that it seems to confuse the user name with the data base name:

root@nakama1-nyc3-01:~/nakama# ./nakama migrate up --database.address “user:password@nakama-db-postgresql-nyc1-55357-do-user-6724142-0.db.ondigitalocean.com:25060/nakama?sslmode=require”
{“level”:“info”,“ts”:“2019-11-07T16:54:27.819Z”,“msg”:“Database connection”,“dsn”:“user:password@nakama-db-postgresql-nyc1-55357-do-user-6724142-0.db.ondigitalocean.com:25060/nakama?sslmode=require”}
{“level”:“fatal”,“ts”:“2019-11-07T16:54:27.864Z”,“msg”:“Error pinging database”,“error”:“FATAL: database “user” does not exist (SQLSTATE 3D000)”,“stacktrace”:“github.com/heroiclabs/nakama/v2/migrate.Parse\n\tgithub.com/heroiclabs/nakama/v2@/migrate/migrate.go:147\nmain.main\n\tgithub.com/heroiclabs/nakama/v2@/main.go:79\nruntime.main\n\truntime/proc.go:203”}

Is it an issue with the user having a password?

7

No, it’s a quirk of Postgres. By default users connect to a ‘database’ (schema) named the same as their username. Nakama takes advantage of this so it can check if the database ("nakama" in your case) is correctly set up. Create an empty database named the same as your user, it won’t be used beyond the migration process.

1 Like
8

Thanks, creating a database with the same name as the user seemed to have fixed things.