link#

• When you say “extract files to PostgreSQL DB files”, this can mean two different things:

  • 1. ✅ Import external files (CSV, JSON, etc.) into tables
  • 2. ✅ Extract/export data from PostgreSQL into files
  • 3. ⚠️ Directly modifying PostgreSQL’s internal database files (not recommended)

• I’ll clearly separate them.

PostgreSQL backup(전체백업, Full physical DB copy)#

pg_basebackup -D backup_folder -Fp -Xs -P

1. Summary (Clear Difference)#

2. Where PostgreSQL Stores Its Real DB Files#

• PostgreSQL stores internal data files in its data directory.

• On Linux typically:

/var/lib/postgresql/<version>/main/

• On macOS (Homebrew):

/opt/homebrew/var/postgresql@<version>/

• Inside you’ll see: (global에 저장 되나봄)

base/
global/
pg_wal/

Visualizing Internal Storage#

data_directory/
├── base/
│   ├── 16384/
│   │   ├── 12345
│   │   ├── 12346
│
├── global/
├── pg_wal/

2️⃣ Extract PostgreSQL Data → File#

• Now the reverse direction.

COPY users FROM '/absolute/path/data.csv' CSV HEADER;

pg_dump

pg_dump -U postgres mydb > backup.sql

pg_dump -Fc mydb > backup.dump

pg_restore -U postgres -d mydb backup.dump

3️⃣ Where PostgreSQL Stores Its Real DB Files#

• PostgreSQL stores internal data files in its data directory.

/var/lib/postgresql/<version>/main/

/opt/homebrew/var/postgresql@<version>/

base/
global/
pg_wal/

data_directory/
├── base/
│   ├── 16384/
│   │   ├── 12345
│   │   ├── 12346
│
├── global/
├── pg_wal/

SHOW data_directory;

4️⃣ If You Meant “Extract Raw Database Files”#

• PostgreSQL is different from file-based databases like:
  • SQLite
• SQLite uses:

mydb.sqlite

• PostgreSQL does NOT work like that.

• PostgreSQL is a server-based database.

• You cannot simply copy one file to move a database.

• Instead, use:

  • pg_dump
  • pg_basebackup (for full physical backup)

5️⃣ Physical Backup (Real DB Files)#

• To copy actual internal database files safely:
• Stop PostgreSQL first OR use:

pg_basebackup -D backup_folder -Fp -Xs -P

• This copies real database storage safely.