Verify a PostgreSQL Practice Database and Its Persistence

A running PostgreSQL container does not prove that you connected to the intended database or that its data will survive replacing the container. This lab checks both: write a unique marker, record the cluster identity, restart the server, then replace the container while reusing its named volume.

Use PowerShell on Windows with a verified local Linux Docker engine. The generated names and labels keep cleanup limited to this practice lab. For the course schema and first-query sequence, use the PostgreSQL setup lesson. The Docker verification checklist can help if the local engine is not ready.

Create a separately named practice instance

Keep one PowerShell session open for the whole exercise, and record the container name, volume name and label printed below. The example expects the verified local desktop-linux context. It publishes no ports and uses --network none; commands reach the server through docker exec. The documented password is only for this isolated disposable lab.

$pgContext = 'desktop-linux'
$pgEndpoint = docker context inspect $pgContext --format '{{.Endpoints.docker.Host}}'
if ($LASTEXITCODE -ne 0 -or $pgEndpoint -notlike 'npipe:////./pipe/*') {
    throw 'Select the verified local Windows Docker context before continuing.'
}
$pgOs = docker --context $pgContext info --format '{{.OSType}}'
if ($LASTEXITCODE -ne 0 -or $pgOs -ne 'linux') { throw 'A Linux engine is required.' }
$labId = [guid]::NewGuid().ToString('N').Substring(0,12)
$pgName = 'xtdemo-pg-' + $labId
$pgVolume = 'xtdemo-pg-data-' + $labId
"Container: $pgName; volume: $pgVolume; label: $labId"

function Start-LabDatabase {
    docker --context $pgContext run -d --name $pgName --network none --label "com.xtensyon.practice=$labId" --mount "type=volume,source=$pgVolume,target=/var/lib/postgresql" -e POSTGRES_USER=practice -e POSTGRES_DB=practice_lab -e POSTGRES_PASSWORD=practice-only postgres:18.6
    if ($LASTEXITCODE -ne 0) { throw 'Could not start the practice container.' }
}
function Wait-LabDatabase {
    for ($labAttempt = 0; $labAttempt -lt 60; $labAttempt++) {
        docker --context $pgContext exec $pgName pg_isready -h 127.0.0.1 -U practice -d practice_lab *> $null
        if ($LASTEXITCODE -eq 0) { return }
        Start-Sleep -Milliseconds 500
    }
    throw 'Database not ready; inspect this container with docker logs.'
}

docker --context $pgContext volume create --label "com.xtensyon.practice=$labId" $pgVolume
if ($LASTEXITCODE -ne 0) { throw 'Could not create the practice volume.' }
Start-LabDatabase
Wait-LabDatabase

The named volume is mounted at /var/lib/postgresql, the official image's volume location for PostgreSQL 18. Its version-specific data directory sits below that mount. This recipe pins postgres:18.6; older major versions have different default paths, so do not transfer the mount rule blindly.

The readiness loop checks the server's TCP listener inside the container. It waits for the final server rather than relying on a fixed delay or merely seeing an “Up” container. If it times out, inspect only this container with docker --context $pgContext logs $pgName before continuing.

Identify the database and write a marker

Run the next block in the same session. It creates one row whose marker contains the generated container name. The output should identify database practice_lab, user practice, and that marker. It also records the cluster's system identifier and displays the named-volume mount.

$markerSql = "CREATE TABLE lab_marker (id integer PRIMARY KEY, note text NOT NULL); INSERT INTO lab_marker VALUES (1, '$pgName'); SELECT current_database() AS database, current_user AS db_user, note AS marker FROM lab_marker;"
docker --context $pgContext exec $pgName psql -X -v ON_ERROR_STOP=1 -U practice -d practice_lab -c $markerSql
if ($LASTEXITCODE -ne 0) { throw 'Marker creation failed.' }
$clusterBefore = docker --context $pgContext exec $pgName psql -X -At -U practice -d practice_lab -c 'SELECT system_identifier FROM pg_control_system();'
if ($LASTEXITCODE -ne 0) { throw 'Could not read the cluster identity.' }
docker --context $pgContext inspect $pgName --format '{{range .Mounts}}{{.Name}} -> {{.Destination}}{{println}}{{end}}'
$clusterBefore

The marker proves which practice data you reached; current_database() and current_user prove the connection identity. The system identifier distinguishes this initialized cluster from another cluster with the same database name. Keep that value for the comparisons below. -X avoids psql startup-file changes, while ON_ERROR_STOP makes a failed SQL statement stop the check.

Restart and compare the same cluster

docker --context $pgContext restart $pgName
if ($LASTEXITCODE -ne 0) { throw 'Restart failed.' }
Wait-LabDatabase

After readiness returns, run this verification block. It must show the original marker and print PASS only when the cluster identity matches:

docker --context $pgContext exec $pgName psql -X -v ON_ERROR_STOP=1 -U practice -d practice_lab -c 'SELECT current_database(), current_user, note FROM lab_marker WHERE id = 1;'
if ($LASTEXITCODE -ne 0) { throw 'Marker verification failed.' }
$clusterAfter = docker --context $pgContext exec $pgName psql -X -At -U practice -d practice_lab -c 'SELECT system_identifier FROM pg_control_system();'
if ($LASTEXITCODE -ne 0 -or $clusterBefore -ne $clusterAfter) { throw 'Unexpected cluster identity.' }
'PASS: same cluster and practice marker.'

This restart keeps the same container. It establishes a useful first checkpoint, but the next test is stronger: recreate the server container without discarding its data volume.

Replace the container while retaining its volume

Stop and remove only the generated lab container, then call the same start function. The volume remains available under $pgVolume.

docker --context $pgContext stop $pgName
if ($LASTEXITCODE -ne 0) { throw 'Stop failed.' }
docker --context $pgContext rm $pgName
if ($LASTEXITCODE -ne 0) { throw 'Container removal failed.' }
Start-LabDatabase
Wait-LabDatabase

Run the previous verification block again. Expect a new container ID, the same cluster identifier, and the same marker row in practice_lab. That shows the data lived in the named volume through container replacement. It does not turn the volume into a backup; keep backups for data you need.

Diagnose a missing marker before deleting anything

• Wrong database: query current_database(). The same cluster can contain several databases with different tables.
• Wrong cluster: compare the recorded system identifier and the context/container name.
• New empty cluster after replacement: inspect the mount name and target path. A different volume or the wrong version-specific mount can select different storage.
• Server is not ready: read the generated container's logs. Repeated container deletion cannot repair an unknown startup failure.

For a quick exercise, connect to database postgres instead of practice_lab in this same container. Does the absence of lab_marker there mean the practice data disappeared?

Answer: no. You selected a different database in the same cluster. Reconnect with -d practice_lab and repeat the identity/marker check. Diagnose the connection before assuming a persistence failure.

Remove only this completed practice lab

The final volume-removal command permanently deletes this lab's database files. Run it only when the exercise is finished and you no longer need its marker. The label guards below require the recorded lab ID to match both resources before removal. Keep the same session variables, or restore the exact recorded values; never substitute an existing course or application database name.

$containerLabel = docker --context $pgContext inspect $pgName --format '{{index .Config.Labels "com.xtensyon.practice"}}'
if ($LASTEXITCODE -ne 0 -or $containerLabel -ne $labId) { throw 'Container label does not match this lab.' }
$volumeLabel = docker --context $pgContext volume inspect $pgVolume --format '{{index .Labels "com.xtensyon.practice"}}'
if ($LASTEXITCODE -ne 0 -or $volumeLabel -ne $labId) { throw 'Volume label does not match this lab.' }
docker --context $pgContext stop $pgName
if ($LASTEXITCODE -ne 0) { throw 'Stop failed.' }
docker --context $pgContext rm $pgName
if ($LASTEXITCODE -ne 0) { throw 'Container removal failed.' }
docker --context $pgContext volume rm $pgVolume
if ($LASTEXITCODE -ne 0) { throw 'Volume removal failed.' }

Remove the container before its volume. The cleanup uses exact names and label checks rather than a broad prune. No other database container or volume needs to be stopped or removed.

References: official PostgreSQL image volume layout and PostgreSQL session information.