{"action":"created","issue":{"url":"https://api.github.com/repos/PostgresApp/PostgresApp/issues/241","labels_url":"https://api.github.com/repos/PostgresApp/PostgresApp/issues/241/labels{/name}","comments_url":"https://api.github.com/repos/PostgresApp/PostgresApp/issues/241/comments","events_url":"https://api.github.com/repos/PostgresApp/PostgresApp/issues/241/events","html_url":"https://github.com/PostgresApp/PostgresApp/issues/241","id":53033310,"number":241,"title":"pg_upgrade issues migrating data during 9.3 to 9.4 upgrade","user":{"login":"albertlyu","id":1390356,"avatar_url":"https://avatars.githubusercontent.com/u/1390356?v=3","gravatar_id":"","url":"https://api.github.com/users/albertlyu","html_url":"https://github.com/albertlyu","followers_url":"https://api.github.com/users/albertlyu/followers","following_url":"https://api.github.com/users/albertlyu/following{/other_user}","gists_url":"https://api.github.com/users/albertlyu/gists{/gist_id}","starred_url":"https://api.github.com/users/albertlyu/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/albertlyu/subscriptions","organizations_url":"https://api.github.com/users/albertlyu/orgs","repos_url":"https://api.github.com/users/albertlyu/repos","events_url":"https://api.github.com/users/albertlyu/events{/privacy}","received_events_url":"https://api.github.com/users/albertlyu/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":4,"created_at":"2014-12-29T10:51:58Z","updated_at":"2016-01-13T09:09:01Z","closed_at":null,"body":"I've since resolved this, but I wanted to report what I remember about the issues I had with upgrading 9.3 to 9.4. I used the instructions under 'Migrate data using pg_upgrade' under [this link](http://postgresapp.com/documentation/install.html). I completed each of Steps 1-4 exactly as written, then proceeded to run the following ```pg_upgrade``` command in my Terminal:\r\n\r\n```\r\n$ /Applications/Postgres.app/Contents/Versions/9.4/bin/pg_upgrade \\\r\n$ -d \"/Users/albertlyu/Library/Application Support/Postgres/var-9.3\" \\\r\n$ -D \"/Users/albertlyu/Library/Application Support/Postgres/var-9.4\" \\\r\n$ -b /Applications/Postgres.app/Contents/Versions/9.3/bin \\ \r\n$ -B /Applications/Postgres.app/Contents/Versions/9.4/bin \\\r\n$ -v\r\n```\r\n\r\n(Note that I had to explicitly specify the right ```pg_upgrade``` 9.4 executable instead of the 9.3 one) In my first execution of that ```pg_upgrade``` script, I found the following in my ```pg_upgrade_utility.log```:\r\n\r\n```\r\nCREATE ROLE \"postgres\";\r\nCREATE ROLE\r\nALTER ROLE \"postgres\" WITH SUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION PASSWORD 'md532e12f215ba27cb750c9e093ce4b5127';\r\nALTER ROLE\r\nCREATE DATABASE \"albertlyu\" WITH TEMPLATE = template0 OWNER = \"albertlyu\";\r\npsql:pg_upgrade_dump_globals.sql:43: ERROR:  database \"albertlyu\" already exists\r\n```\r\n\r\nSo I dropped my 'albertlyu' database, and ran the upgrade script again. This time, I found the following in my Terminal output:\r\n\r\n```\r\nChecking database user is a superuser                       executing: SELECT rolsuper, oid FROM pg_catalog.pg_roles WHERE rolname = current_user\r\nexecuting: SELECT COUNT(*) FROM pg_catalog.pg_roles \r\nok\r\n\r\nOnly the install user can be defined in the new cluster.\r\nFailure, exiting\r\n\"/Applications/Postgres.app/Contents/Versions/9.4/bin/pg_ctl\" -w -D \"/Users/albertlyu/Library/Application Support/Postgres/var-9.4\" -o \"\" -m fast stop >> \"pg_upgrade_server.log\" 2>&1\r\n```\r\n\r\nSo I had to drop my non-install users (i.e. every user not named 'albertlyu', including 'postgres') after [reading this](http://www.postgresql.org/message-id/20130509171152.GC24522@momjian.us). I also dropped my 'albertlyu' database again. Only after this did I get the 'Upgrade Complete' message and did my data files get successfully migrated to the new data directory.\r\n\r\nSo my question is whether I made any mistakes along the way (given what I remember) or if this is a potential bug in Postgres.app dealing with ```pg_upgrade``` from 9.3 to 9.4? Installing the new Postgres 9.4 version automatically created 'albertlyu' as my superuser and the default database 'albertlyu', but the ```pg_upgrade``` script appears to try to migrate 'albertlyu' where 'albertlyu' already exists. Does my reasoning make sense here, or is this a non-issue? Whatever the case, I was able to successfully upgrade, but wanted to submit this issue here in case anyone else has similar issues."},"comment":{"url":"https://api.github.com/repos/PostgresApp/PostgresApp/issues/comments/171221616","html_url":"https://github.com/PostgresApp/PostgresApp/issues/241#issuecomment-171221616","issue_url":"https://api.github.com/repos/PostgresApp/PostgresApp/issues/241","id":171221616,"user":{"login":"jace","id":26695,"avatar_url":"https://avatars.githubusercontent.com/u/26695?v=3","gravatar_id":"","url":"https://api.github.com/users/jace","html_url":"https://github.com/jace","followers_url":"https://api.github.com/users/jace/followers","following_url":"https://api.github.com/users/jace/following{/other_user}","gists_url":"https://api.github.com/users/jace/gists{/gist_id}","starred_url":"https://api.github.com/users/jace/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/jace/subscriptions","organizations_url":"https://api.github.com/users/jace/orgs","repos_url":"https://api.github.com/users/jace/repos","events_url":"https://api.github.com/users/jace/events{/privacy}","received_events_url":"https://api.github.com/users/jace/received_events","type":"User","site_admin":false},"created_at":"2016-01-13T09:09:01Z","updated_at":"2016-01-13T09:09:01Z","body":"For anyone here trying to upgrade from 9.4 to 9.5, here's what you need to do:\r\n\r\n1. Copy the `Contents/Versions/9.4` folder from the old app into the new app, as instructed. DO NOT RUN THE NEW APP YET.\r\n2. Shut down the running server, if any.\r\n3. Ensure the new app is now at `/Applications/Postgres.app`\r\n4. If you did run the app, remove the default cluster it made for you: `rm -rf ~/Library/Application\\ Support/Postgres/var-9.5` (**Warning:** Don't do this if you have data in the 9.5 cluster)\r\n5. Create an empty cluster: `initdb -D ~/Library/Application\\ Support/Postgres/var-9.5`\r\n6. Run the upgrade: `/Applications/Postgres.app/Contents/Versions/9.5/bin/pg_upgrade -b /Applications/Postgres.app/Contents/Versions/9.4/bin -B /Applications/Postgres.app/Contents/Versions/9.5/bin -d ~/Library/Application\\ Support/Postgres/var-9.4 -D ~/Library/Application\\ Support/Postgres/var-9.5 -v`\r\n7. PROFIT!"}}