Create database and user with psql script for Postgres

Postgres is always a popular choice for many applications. It offers many features, hence it was used for my React on Rails App. When it comes to upgrading, I prefer to re-create my db on new server, then depends on rake task db:setup_or_migrate to perform db setup later. Data migration is not the scope of this post.

After installing/upgrading postgres via Postgres.app you can launch psql with the command on terminal:

psql

Once you are in the psql console,

To Create User/Role:

CREATE ROLE kyle WITH LOGIN;

OR

CREATE USER kyle;

the difference is that CREATE USER automatically gives the role login privilege.

Then, enter \du command to print the list of roles to check your newly created role - kyle.

Once created, to set up password:

\password kyle

you will be prompted to insert password

To grant permission for creating database:

ALTER USER kyle CREATEDB;

Also, enter \du to check your newly assigned role attributes (permission).

To grant permission to user as superuser:

ALTER USER kyle SUPERUSER;

To create database:

CREATE DATABASE kylefoo;

Then, enter \l command to print the list of database to check your newly created database.

To give ownership of database to user:

ALTER DATABASE kylefoo OWNER TO kyle;

You are done with creating database kylefoo with owner kyle assigned as owner. You can use the credentials in the database.yml on your rails application. Cheers!

Next, to cover usage of pg_dump and restore for data migration.