PostgreSQL是否有类似Microsoft SQL Server LocalDB的浅数据库功能？

Great question! PostgreSQL does have several features that mirror the convenience of SQL Server LocalDB's shallow databases—perfect for developers needing fast, low-overhead test environments without copying full datasets. Let's dive into the most useful ones:

1. Template Databases #

PostgreSQL’s template database system is the closest direct equivalent. You can create a template database with your preferred schema, extensions, and even sample data, then spin up new databases from it in seconds—no need to replicate all data from a full production dump.

• The default templates are template0 (a clean, unmodified base for fresh databases) and template1 (a customizable template that inherits to new databases by default).
• To create a new database from a custom template, use this command:

  CREATE DATABASE my_test_db TEMPLATE my_development_template;
  

• This is ideal for integration testing where you need consistent pre-configured environments that boot up quickly.

2. Fast Database Cloning with pg_basebackup #

If you need a near-instant snapshot of a running database (say, a copy of your staging environment for testing), pg_basebackup creates a binary file-level copy of the database cluster. This is way faster than a logical dump/restore, and avoids duplicating data unnecessarily at the SQL level.

• For single-database cloning, you can pair it with tablespace configurations or use pg_dump --schema-only if you only need the structure, but pg_basebackup shines for full, fast cluster snapshots.

3. Temporary Databases (Ephemeral Test Environments) #

For ultra-lightweight, short-lived test databases, PostgreSQL supports temporary databases tied to your current session. They’re automatically dropped when you disconnect, making them perfect for quick throwaway tests.

• Create one with:

  CREATE TEMPORARY DATABASE temp_test_db;
  

• Note: Temporary databases are limited to your active session, so they’re best for individual test runs rather than shared testing environments.

4. Dockerized PostgreSQL (Portable, Pre-Configured Environments) #

While not a built-in PostgreSQL feature, many developers use Docker to replicate the "shallow database" workflow. You can build a Docker image with your pre-configured schema, extensions, and sample data, then spin up a fresh container in seconds whenever you need a test environment.

• This approach gives you full portability—you can share the image with your team, and everyone gets an identical test DB without manual setup.

Quick Recap #

• Template databases: Best for reusable, pre-configured test environments.
• pg_basebackup: Ideal for fast snapshots of larger databases.
• Temporary databases: Great for short-lived, session-bound tests.
• Docker: Perfect for portable, shareable test environments across teams.

内容的提问来源于stack exchange，提问作者bogdan.mustiata