DEV Community

Cover image for Create database and user with psql script for Postgres
Kyle Foo
Kyle Foo

Posted on • Edited on

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
Enter fullscreen mode Exit fullscreen mode

screenshot

Once you are in the psql console,

To Create User/Role:

CREATE ROLE kyle WITH LOGIN;
Enter fullscreen mode Exit fullscreen mode

OR

CREATE USER kyle;
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

you will be prompted to insert password

To grant permission for creating database:

ALTER USER kyle CREATEDB;
Enter fullscreen mode Exit fullscreen mode

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

To grant permission to user as superuser:

ALTER USER kyle SUPERUSER;
Enter fullscreen mode Exit fullscreen mode

To create database:

CREATE DATABASE kylefoo;
Enter fullscreen mode Exit fullscreen mode

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

To give ownership of database to user:

ALTER DATABASE kylefoo OWNER TO kyle;
Enter fullscreen mode Exit fullscreen mode

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.

Top comments (0)