Getting Started

Contents

Getting Started#

PostgreSQL, often referred to as Postgres, is an advanced, open-source object-relational database management system (ORDBMS) that extends the SQL language combined with many features that safely store and scale complex data workloads.

👉 New to App-Generator? Join our 10k+ Community using GitHub One-Click SignIN.

Key Features of PostgreSQL include:

  • ACID compliance

  • Multi-Version Concurrency Control (MVCC)

  • Robust transactional support

  • Extensibility (custom functions, operators, data types)

  • Advanced indexing techniques

  • Full-text search

  • JSON and JSONB support

  • Table inheritance and partitioning

  • Procedural languages (PL/pgSQL, PL/Python, PL/Perl, etc.)

  • Foreign data wrappers

To interact with PostgreSQL via terminal, you’ll use the PostgreSQL Command-Line Client. Here are the steps to create a database, user, and set privileges:

  • Accessing PostgreSQL. Enter the root password when prompted.

sudo -u postgres psql

  • Creating a database

CREATE DATABASE mydb;

  • Creating a user

CREATE USER myuser WITH ENCRYPTED PASSWORD 'mypassword';

  • Granting privileges. This grants all privileges on mydb to myuser.

GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;

  • Connecting to the new database

/c mydb

  • Granting schema privileges

GRANT ALL ON SCHEMA public TO myuser;

  • Verifying user privileges

/du myuser

For more granular control, you can specify individual privileges:

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO myuser;

  • Revoke privileges:

REVOKE ALL PRIVILEGES ON DATABASE mydb FROM myuser;

As you advance, explore features like custom extensions, foreign data wrappers, and parallel query execution to leverage PostgreSQL’s full potential in your applications.

Contents