Snippets: PostgreSQL: Scripts for Create / Drop Database

28th December 2020 at 10:40am
Snippets: PostgreSQL

创建和销毁 PG 数据库、用户的脚本。

假设项目名为 issue_board。一般而言先通过 sudo -iu postgres 变用户,然后有两种方式做操作:

通用 SQL 命令操作

/*
This file is used to bootstrap development database.

Run this command:
    psql -U postgres -f sql/create_database.sql

Note: ONLY development database;
*/

CREATE USER issue_board_admin SUPERUSER;   /* LOGIN PASSWORD 'password' */
CREATE DATABASE issue_board OWNER issue_board_admin ENCODING 'utf-8';
/*
This file is used to drop development database;

Run this command:
    psql -U postgres -f sql/drop_database.sql

Note: ONLY development database;
*/

DROP DATABASE issue_board;
DROP USER issue_board_admin;

通过命令行工具操作

# Create a new postgres database superuser, enter the password
createuser -s -P issue_board_admin

# Create a new database owned by user created above
createdb -l en_US.UTF-8 -O issue_board_admin issue_board
dropdb issue_board
dropuser issue_board_admin