PostgresSQL Basic Usage: Creating a database, user, connecting, backing up/restoring a database

Jul 21, 2022#postgres42

AI Translation

This post is translated from Chinese into English through AI.View Original

AI-generated summary

This is a guide on basic usage of PostgresSQL, including creating databases, users, connections, and backing up/restoring databases. It provides commands for switching to the superuser, entering psql, creating users and databases, viewing role and database lists, changing ownership of databases, connecting to databases using psql, backing up databases using pg_dump, and restoring databases using psql. The guide is specific to a Debian 11 system environment.

System Environment:
Debian 11

Switch to superuser:#

    sudo su postgres

Enter psql:#

    psql

Create a user:#

    CREATE USER username WITH PASSWORD 'password';

View role list:#

\du

Create a database specifying owner and encoding:#

    CREATE DATABASE dbname WITH OWNER username ENCODING UTF8;

Create a database:#

    CREATE DATABASE dbname;

View database list:#

\l

Change ownership of the newly created database:#

    ALTER DATABASE dbname OWNER TO username;

Connect to the database using psql:#

    psql -h 127.0.0.1 -p 5432 -U username -d dbname

Backup the database:#

    pg_dump dbname > dbname.dump

Restore the database:#

    psql -f dbname.dump -d dbname