PostgreSQL: PostgreSQL数据库创建与管理

-- Create databases with different locales for each environment
CREATE DATABASE shop_dev
    WITH ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.utf8'
    LC_CTYPE = 'en_US.utf8'
    TEMPLATE = template1;

CREATE DATABASE shop_staging
    WITH ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.utf8'
    LC_CTYPE = 'en_US.utf8';

CREATE DATABASE shop_prod
    WITH ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.utf8'
    LC_CTYPE = 'en_US.utf8';

CREATE DATABASE name
    [WITH]
    [OWNER = user_name]
    [TEMPLATE = template]
    [ENCODING = encoding_name]
    [LC_COLLATE = lc_collate]
    [LC_CTYPE = lc_ctype]
    [TABLESPACE = tablespace_name]
    [CONNECTION LIMIT = conn_limit]
    [IS_TEMPLATE = true | false];

-- Create a simple database with default settings
CREATE DATABASE my_app;

-- Verify creation
\l

CREATE TABLE

-- Create database with all common options specified
CREATE DATABASE shop_prod
    WITH OWNER = devuser
    ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.utf8'
    LC_CTYPE = 'en_US.utf8'
    CONNECTION LIMIT = 100;

CREATE TABLE

-- Step 1: Connect to template1 and add common extensions
\c template1

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pg_trgm";

-- Step 2: Now all new databases will have these extensions
\c postgres

CREATE DATABASE new_project;
-- new_project automatically has uuid-ossp and pg_trgm

-- Step 3: Verify
\c new_project
\dx

CREATE TABLE

-- If template1 has a different encoding than you need,
-- use template0 to create a database with specific encoding
CREATE DATABASE db_with_latin
    TEMPLATE = template0
    ENCODING = 'LATIN1'
    LC_COLLATE = 'C'
    LC_CTYPE = 'C';

CREATE TABLE

-- Rename a database (must disconnect all users first)
ALTER DATABASE shop_dev RENAME TO shop_development;

-- Set default search_path for a database
ALTER DATABASE shop_prod SET search_path TO public, admin;

-- Set work_mem for a specific database
ALTER DATABASE shop_prod SET work_mem = '64MB';

-- Change connection limit
ALTER DATABASE shop_prod CONNECTION LIMIT = 200;

-- Mark as template
ALTER DATABASE shop_base IS_TEMPLATE = true;

-- SQL 语句执行成功

-- Find active connections to a database
SELECT pid, usename, application_name, client_addr, state
FROM pg_stat_activity
WHERE datname = 'shop_dev';

DROP DATABASE [IF EXISTS] name [WITH (FORCE)];

-- Safe delete (no error if database doesn't exist)
DROP DATABASE IF EXISTS old_project;

-- Force delete (disconnect all users first, PG 13+)
DROP DATABASE IF EXISTS shop_dev WITH (FORCE);

-- SQL 语句执行成功

-- List available encodings
SELECT name, description FROM pg_encodings LIMIT 10;

-- List available collations
SELECT name, locale FROM pg_collation LIMIT 10;

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

-- Create a test table with text data
CREATE TABLE sort_test (name TEXT);
INSERT INTO sort_test VALUES ('apple'), ('Banana'), ('cherry'), ('Apple');

-- Sort with C locale (byte order: uppercase first)
SELECT name FROM sort_test ORDER BY name COLLATE "C";

-- Sort with en_US.utf8 (dictionary order: case-insensitive within same letter)
SELECT name FROM sort_test ORDER BY name COLLATE "en_US.utf8";

-- Clean up
DROP TABLE sort_test;

-- C locale:
  Apple
  Banana
  apple
  cherry

-- en_US.utf8:
  apple
  Apple
  Banana
  cherry

# List all databases
\l

# Switch to shop_dev database
\c shop_dev

# Check current connection
\conninfo

# Switch back to postgres database
\c postgres

# 命令执行成功

-- ============================================
-- Complete example: set up dev/staging/prod databases
-- For an e-commerce project
-- ============================================

-- Step 1: Connect to postgres (maintenance database)
\c postgres

-- Step 2: Create dev database with English locale
CREATE DATABASE shop_dev
    WITH ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.utf8'
    LC_CTYPE = 'en_US.utf8'
    OWNER = devuser
    CONNECTION LIMIT = 50;

-- Step 3: Create staging database (same config as prod)
CREATE DATABASE shop_staging
    WITH ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.utf8'
    LC_CTYPE = 'en_US.utf8'
    OWNER = devuser
    CONNECTION LIMIT = 50;

-- Step 4: Create prod database with connection limit
CREATE DATABASE shop_prod
    WITH ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.utf8'
    LC_CTYPE = 'en_US.utf8'
    OWNER = devuser
    CONNECTION LIMIT = 200;

-- Step 5: Set default search_path for prod
ALTER DATABASE shop_prod SET search_path TO public, admin;

-- Step 6: Verify all databases created
SELECT datname, pg_encoding_to_char(encoding) AS encoding,
       datcollate, datctype, datconnlimit
FROM pg_database
WHERE datname LIKE 'shop_%'
ORDER BY datname;

   datname    | encoding |  datcollate  |   datctype   | datconnlimit
--------------+----------+--------------+--------------+-------------
 shop_dev     | UTF8     | en_US.utf8   | en_US.utf8   |           50
 shop_prod    | UTF8     | en_US.utf8   | en_US.utf8   |          200
 shop_staging | UTF8     | en_US.utf8   | en_US.utf8   |           50