PostgreSQL: 如何安装PostgreSQL并完成首次连接

# Pull and run PostgreSQL 17 in Docker
docker run --name my-postgres \
  -e POSTGRES_PASSWORD=mysecretpassword \
  -e POSTGRES_USER=bob \
  -e POSTGRES_DB=dev_shop \
  -p 5432:5432 \
  -d postgres:17

# Verify the container is running
docker ps | grep my-postgres

# Step 1: Pull PostgreSQL 17 image
docker pull postgres:17

# Step 2: Run container with custom settings
docker run --name pg-dev \
  -e POSTGRES_USER=devuser \
  -e POSTGRES_PASSWORD=devpass123 \
  -e POSTGRES_DB=shopdb \
  -p 5432:5432 \
  -v pgdata:/var/lib/postgresql/data \
  -d postgres:17

# Step 3: Verify container is running
docker ps

# Step 4: Connect via psql inside container
docker exec -it pg-dev psql -U devuser -d shopdb

psql (17.0)
Type "help" for help.

shopdb=>

1. Download installer from https://www.enterprisedb.com/downloads/postgres
2. Run postgresql-17-windows-x64.exe
3. Select installation directory (default: C:\Program Files\PostgreSQL\17)
4. Set superuser (postgres) password
5. Set port (default: 5432)
6. Select locale (default: [Default locale])
7. Check "Launch Stack Builder at exit" (optional, for additional tools)
8. Click Finish

# Install PostgreSQL 17 via Homebrew
brew install postgresql@17

# Start PostgreSQL service
brew services start postgresql@17

# Connect to default database
psql postgres

# Verify version
SELECT version();

# psql 命令执行成功

# Add PostgreSQL official APT repository
sudo apt update
sudo apt install -y curl ca-certificates
sudo install -d /usr/share/postgresql-common/pgdg
sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc \
  --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc

sudo sh -c 'echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] \
  https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" \
  > /etc/apt/sources.list.d/pgdg.list'

# Install PostgreSQL 17
sudo apt update
sudo apt install -y postgresql-17

# Switch to postgres user and connect
sudo -u postgres psql

# Verify installation
SELECT version();

# psql 命令执行成功

1. Download from https://www.pgadmin.org/download/
2. Install and launch pgAdmin 4
3. Click "Add New Server"
4. General tab: Name = "Local Dev"
5. Connection tab:
   - Host: localhost (or 127.0.0.1)
   - Port: 5432
   - Maintenance database: shopdb
   - Username: devuser
   - Password: devpass123
6. Click Save

1. Download from https://dbeaver.io/download/ (Community Edition)
2. Install and launch DBeaver
3. Click "New Database Connection" (plug icon)
4. Select PostgreSQL
5. Fill connection settings:
   - Host: localhost
   - Port: 5432
   - Database: shopdb
   - Username: devuser
   - Password: devpass123
6. Click "Test Connection" (will prompt to download PG JDBC driver)
7. Click Finish

# Connect to local PostgreSQL
psql -h localhost -p 5432 -U devuser -d shopdb

# Or use connection URI
psql "postgresql://devuser:devpass123@localhost:5432/shopdb"

# Or via Docker
docker exec -it pg-dev psql -U devuser -d shopdb

CONTAINER ID   IMAGE     STATUS    
abc123         latest    Up 2 hours

-- Check connection info
\conninfo

-- List databases
\l

-- Create a test table
CREATE TABLE test_greeting (
    id SERIAL PRIMARY KEY,
    message TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Insert data
INSERT INTO test_greeting (message) VALUES ('Hello, PostgreSQL!');

-- Query data
SELECT * FROM test_greeting;

-- Drop test table
DROP TABLE test_greeting;

-- \conninfo output:
You are connected to database "shopdb" as user "devuser" on host "localhost" at port "5432".

-- SELECT output:
 id |      message       |         created_at
----+--------------------+----------------------------
  1 | Hello, PostgreSQL! | 2026-07-13 10:30:00.123456

# ============================================
# Complete workflow: install, connect, verify
# From zero to running in 5 commands
# ============================================

# 1. Start PostgreSQL via Docker
docker run --name pg-dev \
  -e POSTGRES_USER=devuser \
  -e POSTGRES_PASSWORD=devpass123 \
  -e POSTGRES_DB=shopdb \
  -p 5432:5432 \
  -d postgres:17

# 2. Wait for container to be ready (about 5 seconds)
sleep 5

# 3. Connect via psql
docker exec -it pg-dev psql -U devuser -d shopdb

# 4. Inside psql: run verification queries
# (Type these commands in the psql prompt)

-- Show PostgreSQL version
SELECT version();

-- Show current connection
\conninfo

-- List available extensions
SELECT name, default_version FROM pg_available_extensions LIMIT 5;

-- Create a test table and insert data
CREATE TABLE hello_pg (
    id SERIAL PRIMARY KEY,
    greeting TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

INSERT INTO hello_pg (greeting) VALUES
    ('Hello from PostgreSQL!'),
    ('Docker install works!'),
    ('Ready to learn!');

-- Verify
SELECT * FROM hello_pg;

-- Clean up
DROP TABLE hello_pg;

-- Exit psql
\q

# 5. Stop container when done
docker stop pg-dev

                                                     version
-----------------------------------------------------------------------------------------------------------------
 PostgreSQL 17.0 on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit

     name      | default_version
---------------+-----------------
 plpgsql       | 1.0
 uuid-ossp     | 1.1
 pg_trgm       | 1.6

 id |       greeting        |          created_at
----+-----------------------+-------------------------------
  1 | Hello from PostgreSQL! | 2026-07-13 10:30:00+00
  2 | Docker install works!  | 2026-07-13 10:30:00+00
  3 | Ready to learn!        | 2026-07-13 10:30:00+00