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:
Once you are in the psql console,
To Create User/Role:
CREATE ROLE kyle WITH LOGIN;
CREATE USER kyle;
the difference is that
CREATE USER automatically gives the role login privilege.
\du command to print the list of roles to check your newly created role - kyle.
Once created, to set up password:
you will be prompted to insert password
To grant permission for creating database:
ALTER USER kyle CREATEDB;
\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;
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
restore for data migration.