Home About Contact
PostgreSQL , Docker

PostgreSQL データベースのバックアップとリストア 【複数テーブル編】

前回のポスト PostgreSQL データベースのバックアップとリストア ではデータベースに一つのテーブルしか存在していなかったので、 今回は複数（２つ）のテーブルがあった場合にどうなるか調べてみます。

例によって PostgreSQL を Docker で動かして、ディレクトリ形式でのバックアップとリストアを試します。

環境:

$ lsb_release -a|grep Description
Description:    Ubuntu 24.04.1 LTS

$ docker --version
Docker version 27.2.0, build 3ab4256

$ kotlin -version
Kotlin version 2.1.0-release-394 (JRE 17.0.2+8-86)

まず Postgres を起動 / データベースを作成

次のように docker run して PostgreSQL をパスワードなしで起動:

$ docker run --rm -d -p 5432:5432 --name my-postgre -e POSTGRES_HOST_AUTH_METHOD=trust postgres

psql コマンドで pokemon_db を作成:

$ psql -U postgres -h localhost -c "CREATE DATABASE pokemon_db;"

テーブル作成とデータ追加

そして Kotlin Script でデータを用意:

// psql.main.kts

@file:Repository("https://repo1.maven.org/maven2/")
@file:DependsOn("org.postgresql:postgresql:42.7.5")
@file:DependsOn("com.zaxxer:HikariCP:6.2.1")

import com.zaxxer.hikari.HikariDataSource
import com.zaxxer.hikari.HikariConfig

data class Db(
    val driver: String,
    val url: String,
    val user: String,
    val password: String)

val toConfig: (Db)->HikariConfig = { db->
    HikariConfig().apply {
        setDriverClassName( db.driver )
        setJdbcUrl( db.url )
        setUsername(db.user)
        setPassword(db.password)
    }
}

val db = Db(
    "org.postgresql.Driver",
    "jdbc:postgresql://localhost/pokemon_db",
    "postgres",
    "")

val ds = HikariDataSource(toConfig(db))
ds.connection.use { connection->
    connection.createStatement().use { st->
        // create tables:
        val sql0 = listOf(
            "DROP TABLE IF EXISTS trainers;",
            "CREATE TABLE trainers(id SERIAL PRIMARY KEY, name VARCHAR(128));",
            "DROP TABLE IF EXISTS pokemons;",
            "CREATE TABLE pokemons(id SERIAL PRIMARY KEY, name VARCHAR(128), trainerId INTEGER);",
        ).joinToString("")
        st.executeUpdate(sql0)

        // insert trainers:
        listOf(
            "INSERT INTO trainers (name) VALUES('Satoshi');",
            "INSERT INTO trainers (name) VALUES('Kasumi');",
        ).forEach { sql->
            st.executeUpdate(sql)
        }

        // insert pokemons:
        listOf(
            "INSERT INTO pokemons (name, trainerId) VALUES('Pikachu', 1);",
            "INSERT INTO pokemons (name, trainerId) VALUES('Squirtle', 1);",
            "INSERT INTO pokemons (name, trainerId) VALUES('Psyduck', 2);",
            "INSERT INTO pokemons (name, trainerId) VALUES('Golduck', 2);",
        ).forEach { sql->
            st.executeUpdate(sql)
        }
    }
}

実行します。

$ kotlin psql.main.kts

意図通りデータが作成できたか確認します。

$ psql -U postgres -h localhost -d pokemon_db

pokemon_db=# SELECT * FROM trainers;
 id |  name   
----+---------
  1 | Satoshi
  2 | Kasumi
(2 rows)

pokemon_db=# SELECT * FROM pokemons;
 id |   name   | trainerid 
----+----------+-----------
  1 | Pikachu  |         1
  2 | Squirtle |         1
  3 | Psyduck  |         2
  4 | Golduck  |         2
(4 rows)

いい感じです。

トレーナに対応するポケモンが意図通りかも確認してみます。

pokemon_db=# SELECT trainers.name, pokemons.name FROM trainers, pokemons WHERE trainers.id=pokemons.trainerid;
  name   |   name   
---------+----------
 Satoshi | Pikachu
 Satoshi | Squirtle
 Kasumi  | Psyduck
 Kasumi  | Golduck
(4 rows)

OKです。

バックアップする (pg_dump)

前回同様に pg_dump コマンドを実行:

$ pg_dump -U postgres -h localhost -Fd pokemon_db -f pokemon_db.dump

作成された pokemon_db.dump/ ディレクトリを確認:

.
└── pokemon_db.dump
    ├── 3367.dat.gz
    ├── 3369.dat.gz
    └── toc.dat

テーブルごとに .dat.gz ができる感じなのでしょうか（わかりません）。

リストアしてみる

データベースをリセットするために、 一旦 docker stop して再度 docker run します。

$ docker ps
$ docker stop <CONTAINER ID>
$ docker run --rm -d -p 5432:5432 --name my-postgre -e POSTGRES_HOST_AUTH_METHOD=trust postgres

データベースを作成:

$ psql -U postgres -h localhost -c "CREATE DATABASE pokemon_db;"

リストアを実行:

$ pg_restore -U postgres -h localhost --verbose -d pokemon_db pokemon_db.dump

リストアできたか確認:

$ psql -U postgres -h localhost -d pokemon_db
psql (17.3 (Ubuntu 17.3-1.pgdg24.04+1))
Type "help" for help.

pokemon_db=# SELECT trainers.name, pokemons.name FROM trainers, pokemons WHERE trainers.id=pokemons.trainerid;
  name   |   name   
---------+----------
 Satoshi | Pikachu
 Satoshi | Squirtle
 Kasumi  | Psyduck
 Kasumi  | Golduck
(4 rows)

意図通りリストアできました。

以上です。