PostgreSQL is a free and open source relational database that dates to 1986. ACID compliance dates to 2001 and the database stores JSON and arrays beside classic tables. Export one database with pg_dump and import archive formats with pg_restore. Plain SQL dumps import with psql.
Teams select PostgreSQL for web apps and analytics and need to know what it includes and how to protect its data.
Facts below come from the PostgreSQL About page and the official manual pages for pg_dump and pg_restore.
What is PostgreSQL?
PostgreSQL is a free relational database with open source code. PostgreSQL began in 1986 as POSTGRES at the University of California at Berkeley and has seen nearly 40 years of active development.
PostgreSQL states it has been ACID compliant since 2001 and works on all major operating systems. Version 18 from September 2025 conforms to at least 170 of the 177 mandatory SQL:2023 Core features according to the project.
Which Features does PostgreSQL Include?
| Area | Examples from the Project Page |
|---|---|
| Data types | JSON and JSONB and arrays and ranges and UUID and geometry |
| Integrity | Primary and foreign and exclusion constraints |
| Indexes | B-tree and GiST and GIN and BRIN |
| Concurrency and recovery | MVCC and replication and point in time recovery and table partitioning |
| Security | Row level and column level security |
| Extensibility | Custom types and functions in PL/pgSQL and Perl and Python and Tcl |
How do You Backup a PostgreSQL Database?
pg_dump exports one database as an SQL script or as an archive for pg_restore. It produces consistent exports while the database is in use and it does not stop readers or writers.
| Format | Flag | Restore With |
|---|---|---|
| Plain SQL | -Fp | psql |
| Custom archive | -Fc | pg_restore |
| Directory | -Fd | pg_restore |
| Tar | -Ft | pg_restore |
pg_dump -Fc mydb > db.dump
createdb -T template0 newdb
pg_restore -d newdb db.dumpThe manual states that pg_dump is generally not the right tool for regular production backups outside basic cases. It also warns that restoring a dump executes SQL chosen by the source superusers so inspect dumps from untrusted sources first. Roles and tablespaces need pg_dumpall.
For comparison see Microsoft SQL Server and SQLite and IBM Db2.
Frequently Asked Questions
Is PostgreSQL Free?
Yes. PostgreSQL is described as free and open source.
Is PostgreSQL ACID Compliant?
Yes. ACID compliance dates to 2001 per the project.
Can Users Work During a Dump?
Yes. Users continue working because pg_dump produces consistent exports without stopping readers or writers.
How do I Restore a Plain SQL Dump?
Use psql. pg_restore accepts only custom and directory and tar archives.
Which Systems Run PostgreSQL?
It works on all major operating systems per the project.
Key Takeaways
- PostgreSQL costs nothing and dates to 1986.
- ACID compliance dates to 2001.
- pg_dump exports one database without blocking users.
- pg_restore imports custom and directory and tar archives.
- Plain SQL dumps import with psql.
Sources and References
Facts come from the PostgreSQL project.
- About PostgreSQL (postgresql.org) www.postgresql.org
- pg_dump (PostgreSQL documentation) www.postgresql.org
- pg_restore (PostgreSQL documentation) www.postgresql.org