PostgreSQL Basic Usage: Creating databases, users, connections, and backing up/restoring databases.

Jul 21, 2022#postgres22

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 PostgreSQL, including creating databases, users, connections, and backing up/restoring databases. It provides commands for switching to the superuser, entering the psql command-line interface, creating users and databases, viewing role and database lists, changing database ownership, connecting to a database using psql, backing up a database using pg_dump, and restoring a database using psql.

title: "PostgreSQL Basic Usage: Creating Databases, Users, Connections, Backing Up/Restoring Databases"
date: 2022-07-22T14:26:40+08:00
slug: create_use1
type: posts
draft: false
categories: ["PostgreSQL","Using Software"]
tags: ["postgres"]#

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 with specified 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