Postgresql – Postgres COPY with on conflict ignore – possible

If you want to copy a complete PostgreSQL database within its cluster, the fastest method is to use it as TEMPLATE in a CREATE DATABASE statement. I quote the manual:

By default, the new database will be created by cloning the standard system database template1. A different template can be specified by writing TEMPLATE name. In particular, by writing TEMPLATE template0, you can create a virgin database containing only the standard objects predefined by your version of PostgreSQL. This is useful if you wish to avoid copying any installation-local objects that might have been added to template1.

CREATE DATABASE db_copy TEMPLATE db_org;

This effectively copies underlying files around like you tried manually. Except that is sets everything up to work correctly.

You may want to clean up your original before you do (depends):

VACUUM FULL ANALYZE;

will the rows be indexed during "COPY" since the table has an implicit index?

Yes.

if the answer is yes, the "COPY" will slow down?

Yes.

You can LOCK TABLE ... IN ACCESS EXCLUSIVE MODE then drop the PRIMARY KEY constraint for the duration if you need to.

PostgreSQL doesn't have a way to mark indexes as invalid then revalidate them for use without dropping and recreating them (yet).