Installing Postgres 9.4

Here's a reminder checklist of the steps I take when installing successive beta versions  (1, 2, and 3, so far) of Postgres 9.4 on my Macs.

Installer

The Postgres support company EnterpriseDB graciously supplies installers for Mac OS X as a courtesy to the community. You may reach their site via the Download page of the usual Postgres site. For beta versions look for the paragraph labeled "Beta/RC Releases and development snapshots (unstable)", and find the link to click. On the next page, look for the link near the text "offsite link". Currently takes you to the Early Experience page of EnterpriseDB. Click a link to download a DMG file named something like "postgresql-9.4.0-beta3-osx.dmg".

Users

The Postgres superuser "postgres" is already created by the installer.

Next we need to create a not-quite-so-super user. This is done in "Login Roles" in pgAdmin, not the "Group Roles" list. Choose a name and password for this user, and write it down. The create this user in pgAdmin using a dialog or run the following SQL. After creation, refresh pgAdmin, and context-click the user to choose "Properties" where you can define a password  on the "Definition" tab.

CREATE ROLE your_admin_user_name_goes_here LOGIN
  NOSUPERUSER INHERIT CREATEDB CREATEROLE REPLICATION;

Next, create a group role to be used by your application. Let's imagine your app is named "Example" in general and "example_" in Postgres. We move attention from "Login Roles" to the "Group Roles" list in pgAdmin. Again, do this in the pgAdmin wizard-like dialog or use this SQL.

CREATE ROLE example_app_role_
  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;

As done above for the admin user, context-click the user to choose "Properties" where you can define a password  on the "Definition" tab. And, of course, you are writing down these passwords.

Now create a user to be assigned to that role. Again, use either dialog or this SQL:

CREATE ROLE example_app_ LOGIN
  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
GRANT example_app_role_ TO example_app_;
COMMENT ON ROLE example_app_ IS 'For connections from our Example app.';


Restore

As I am installing a succession of beta versions, I already have a backup of my desired database. I used pgAdmin's "Backup" feature to create a .backup file. That chore is described in my previous blog entry.

That backup contains just about every aspect of my database, except one: Users & Passwords. That is why we defined our admin user, app role, and app user in those steps above. Those users and roles must be in place for the restore feature to work. The restore references ownership of various objects by those users/roles.

So now I want to restore that database to my new Postgres. No go. The restore process cannot create the database. You must create the database manually, such as in pgAdmin. Use the same name, but need take no further steps.

Context-click on the new database to choose the Restore feature. In the dialog, choose the character encoding to match the original. Not sure of this is required, but probably. Use the button to choose the .backup file to be imported. Finally click the main button to execute the restore. The restoration may take a while. Eventually look to the "Messages" tab of the window to show the progress and completion. Look at the bottom of that report to see if any errors or issues arose.

Permissions

Now we have users defined and we have a database restored. Now we have combine them. The app role we defined must be given permission to work with that database. Execute SQL such as the following, after you have done your homework to study such permissions.

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO example_app_role_;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO example_app_role_;

Done

That's it. Your database should be back in order. Tables and data should be there. Type "table my_table_name_;" in the SQL window to see all rows, as a quick test. Any functions, domains, etc. you defined should be intact.

Alternative: pg_restore

Instead of this backup-destroy-restore process, you can go another route. Postgres has a nifty pg_restore feature for doing major upgrades in place. I've no experience with this yet.