Databases

Installation

Postgres is an open-source RDBMS that can be hosted independently on a machine, or hosted via a database service using EDB. PostgreSQL can be installed in different ways depending on the operating system that is being used.

Running the system

This section will discuss how to run PostgreSQL on Mac, and how to initiate the DB via the command line.

initdb /usr/local/var/postgres -E utf8

2. Run Postgresql on MacOS

brew services start postgres

3. During the `initdb` phase, a database with the name `<user>` should be created. If this is not created, a manual creation would be necessary.

createdb <user>

4. The psql acts like a command-line client to issue commands to your database. This can be started using the command.

psql

Create a new user and grant permissions

By default, all the databases under Postgres will be created under the main user. This main user contains superuser permissions. Normally you would want to create a database and give permissions to access DB at various levels.

CREATE USER <username>
ALTER USER <username> with encrypted password '<password>';
ALTER USER <username> with CREATEDB;

The other permissions that can be added to the user include the following:

Create role and alter role permissions

Sometimes we require a group of permissions to be given to several users. Instead of adding individual permissions to each user, a role can be created. Any new user that is created can be assigned a particular role.

CREATE ROLE <groupname> WITH <role1>, <role2>;
GRANT <groupname> TO <username>
REVOKE <groupname> TO <username>

Play with PSQL

Command-line tools are especially helpful when you are trying to visualize your database. You’d want to check what databases are present, what type of relations are present in your database. Note that all these commands should be used in PSQL

\du
\l
\c <database name>\dt
psql -h localhost -p 5432 -U postgres
\d "<relation name>"

And finally, now that you know the basics of working with Postgres, it is time to create new databases and explore various functionalities on your own. Just write your queries in a .sql file and execute it using the command:

psql -f <filepath> -q <dbname> <username>

That’s all for today, see you in the next article.

CODER | BLOGGER | ARTIST | GHOST

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store