How to copy a table from one database to another

There are many ways to do this, but the psql client's \copy command can be used to copy data from one table to another. Let's say you have two databases, olddb and newdb. olddb has a table called users, and newdb has a new, empty table called users that is currently empty but needs the data from olddb's users. Here's how to accomplish this on the command line, piping the output of one psql session into a second psql session:

psql \
    -X \
    -U user \
    -h oldhost \
    -d olddb \
    -c "\\copy users to stdout" \
| \
psql \
    -X \
    -U user \
    -h newhost \
    -d newdb \
    -c "\\copy users from stdin"

A NOTE ON FAILING: I've gotten in teh habit of putting two things at the top of all of my bash scripts:

# fail on uninitialized vars rather than treating them as null
set -u
# fail on the first program that returns $? != 0
set -e

However, when two programs run at once (one piping into the other), the exit status of the last program ends up in $?, and the exit status of the first program gets lost. (Slight lie there: The exit status of the last program in the example above is also put in ${PIPESTATUS[1]}, and the exit status of the first program is put in ${PIPESTATUS[0]}.) When I want the shell script to fail on the failure of any program in the pipeline, I then set three things at the top of my shell script:

set -u
set -e
set -o pipefail

A MORE DIFFICULT EXAMPLE: Let's say your users table in olddb has three columns:

• first_name
• middle_name
• last_name

But your users table in newdb has two columns:

• first_name
• last_name

psql \
    -X \
    -U user \
    -h oldhost \
    -d olddb \
    -c "\\copy (select first_name, last_name from users) to stdout" \
| \
psql \
    -X \
    -U user \
    -h newhost \
    -d newdb \
    -c "\\copy users from stdin"

Here's a good way to get the table definition:

pg_dump \
    -U db_user \
    -h db_host \
    -p 55432 \
    --table my_table \
    --schema-only my_db