Copy database
Copy a PostgreSQL database, or some of its tables, into another PostgreSQL database in one step.
Copy database moves a PostgreSQL database into another one: staging into your local database, one server into another, or a few tables between them. It is in the desktop app.
Copy a database
- Open it from a PostgreSQL connection: choose Copy database… in the connection's ⋯ menu on the connections page, or in the command palette while a database is open.
- Under To, pick the database to copy into. Every PostgreSQL database in the workspace is listed, on any connection this device can query. A connection whose server details or password are not saved here is left out until you add them.
- Under Copy, keep Everything or uncheck tables to copy only some.
- Click Copy and replace.
The log shows each table and object as it is created, with the time. Press Stop or Escape to cancel. If the copy fails, the log ends with what blocked it, such as a view in the target that depends on a table being replaced. If your network drops mid-copy, nothing is committed: the target rolls back and frees its tables about half a minute after it loses you, or later when it sits behind an SSH bastion.
What gets copied
| Choice | Copies |
|---|---|
| Everything | Schemas, tables with their data, indexes and constraints, types, functions, views, extensions. |
| Some tables | Those tables with their data, indexes, constraints and sequences. A partitioned table brings its partitions, so the list leaves them out. Their schema is created if the target lacks it. |
Owners and grants are left out, so the copy works on a server where your roles do not exist.
When you copy some tables, the types and functions they use must already be in the target. A table whose column uses an enum the target lacks fails with type ... does not exist.
Replacing and failures
Everything the copy brings replaces the object of the same name in the target. Other objects in the target stay.
The copy runs in one transaction. If anything fails, or you stop it, nothing changes in the target. A common failure is an object in the target that depends on a table being replaced, such as a view: the error names it, and you can drop it and copy again.
The target can't be the source itself. Two connections can reach the same database, so Tamery checks the server before copying and stops if they match.
Versions and connections
Tamery runs pg_dump and psql 18 on your computer, so the source can be PostgreSQL 9.2 to 18. The target can run an older version than the source; an object it cannot create fails the copy like any other error. On Linux, Tamery uses the PostgreSQL client tools you have installed, version 16 or newer.
Connections over SSH tunnels and TLS work the same as everywhere else in Tamery. The one exception is a client key protected by a passphrase: the copy can't use it yet.