Skip to content

Postgres broken? #93

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.

Sign up for GitHub

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

Jump to bottom
Closed
endophage opened this issue Aug 28, 2018 · 5 comments
Closed

Postgres broken? #93

endophage opened this issue Aug 28, 2018 · 5 comments

Comments

@endophage
Copy link

I may be missing something but I'm trying to run a single migration against an empty postgres database and I get the following behaviour:

$> migrate -path postgres -database postgresql://the_user@hostname/db_name -verbose up 1
2018/08/28 21:57:06 Start buffering 20180827162119/u initial
2018/08/28 21:57:06 Read and execute 20180827162119/u initial
2018/08/28 21:57:06 error: pq: relation "schema_migrations" does not exist in line 0: TRUNCATE "schema_migrations"
$> migrate -path postgres -database postgresql://the_user@hostname/db_name -verbose up 1
2018/08/28 22:00:19 error: Dirty database version 20180827162119. Fix and force version.

In the first instance I would have expected migrate to create the schema_migrations table and I see code to do that. I saw another closed issue with the same error and it was suggested the search_path should be set. I tried this and still get the exact same behaviour as above.

Also worth noting, after running the command the first time the database appears to be empty. Only the public schema exists and it has no tables in it. After running the same command again, most but not all of the tables are created and as you can see, there's no useful error on which specific query in the migration failed.
@dhui
Copy link
Member

dhui commented Aug 29, 2018

What version of migrate are you using?

The postgres driver should create the schema_migrations. See ensureVersionTable(). However, it will not create the database. See #21 for the discussion for that topic.

Did you have any successful up or down migrations run? Did you delete any migrations in between the runs?

@endophage
Copy link
Author

Version 3.4.0 installing into the postgres:9.6 image from docker hub. Database was already created.

I finally got it running, seems to be some kind of issue with roles? I don't have any explicit changes of role in the migrations but the ownership of various tables (not schema_migrations) was changed as part of the migration. Postgres is not providing particularly helpful errors but changing the role (in the connection string) I ran the migrations as to the same role that ultimately owned most of the tables (and as such, schema_migrations was owned by that same role), seemed to solve the issue.

@dhui
Copy link
Member

dhui commented Aug 29, 2018

Ahh, yeah, the pg user/role needs the CREATEDB privilege at the db cluster level and should probably have all privileges on the database it's migrating. If you're concerned about security, I'd use a separate user/role for your app and migrations.

@endophage
Copy link
Author

They need CREATEDB to create the schema_migrations table?

@dhui
Copy link
Member

dhui commented Aug 30, 2018

They need CREATEDB to create the schema_migrations table?

Oh, no, I misspoke. The pg user/role doesn't need CREATEDB privileges

@dhui dhui closed this as completed Sep 20, 2018
karolhrdina added a commit to karolhrdina/migrate that referenced this issue Jan 31, 2019
@karolhrdina
PROBLEM: postgres migration broken
8390264 
When i try to run migrate from upstream master i run into the following
problem:
```bash
kj@vrrr:~/repos/migrate-upstream/cli/build> ./migrate.linux-amd64 -source file:///home/kj/work/cncenter.git/behav-api/sql/ -database postgres://postgres:[email protected]:5432/?sslmode=disable -verbose up 1
2019/01/31 18:35:11 Start buffering 1/u initialize_db
2019/01/31 18:35:12 Read and execute 1/u initialize_db
2019/01/31 18:35:12 error: pq: relation "schema_migrations" does not exist in line 0: TRUNCATE "schema_migrations"
```
Postres is complaining:
```
2019-01-31 17:35:12.440 UTC [61] ERROR:  relation "schema_migrations" does not exist
2019-01-31 17:35:12.440 UTC [61] STATEMENT:  TRUNCATE "schema_migrations"
```
I am running postgres like this:
`docker run --rm --net=behavnet --name postgres -p 5432:5432 -v /home/kj/temp:/tmp:rw postgres:10.6`

I found an old closed issue
golang-migrate#93
which suggested that i might need priviliges:
```bash
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
```
Other than that, there was no other suggestion or resolution of that
issue.

With this commit built, i have no more problems:
```bash
kj@vrrr:~/repos/migrate/cli/build> ./migrate.linux-amd64 -source file:///home/kj/work/cncenter.git/behav-api/sql/ -database postgres://postgres:[email protected]:5432/?sslmode=disable -verbose up 1
2019/01/31 18:42:06 Start buffering 1/u initialize_db
2019/01/31 18:42:06 Read and execute 1/u initialize_db
2019/01/31 18:42:06 Finished 1/u initialize_db (read 12.662008ms, ran 155.706703ms)
2019/01/31 18:42:06 Finished after 169.958723ms
2019/01/31 18:42:06 Closing source and database
```

No more errors in postgres log.
@karolhrdina karolhrdina mentioned this issue Jan 31, 2019
Closed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@dhui @endophage