Export table scheme and data separately.

Commands may also change, so please refer to the PostgreSQL documentation for the most up-to-date information.

To import the exported data, execute psql with the -f file_name option.

Add Column

PostgreSQL allows us to add new column only after existing columns. On the other hand, MySQL allows us to add new column wherever position.

So I add column in PostgreSQL in the following procedure.

1. Export table scheme and data separately. (You can also export both data and schema together.)

   Shell

   pg_dump DBNAME -h HOSTNAME -t TABLENAME -U USERNAME -a --file=20130212_data.sql pg_dump DBNAME -h HOSTNAME -t TABLENAME -U USERNAME -s --file=20130212_schema.sql

   1 2	pg_dump DBNAME -h HOSTNAME -t TABLENAME -U USERNAME -a --file=20130212_data.sql pg_dump DBNAME -h HOSTNAME -t TABLENAME -U USERNAME -s --file=20130212_schema.sql

2. Change the exported table scheme, add table deletion sql on the top.

   Shell

   vim 20130212_schema.sql

   1	vim 20130212_schema.sql

3. Create backup.

   Shell

   pg_dump DBNAME -h HOSTNAME -t TABLENAME -U USERNAME --file=20130212_backup.sql

   1	pg_dump DBNAME -h HOSTNAME -t TABLENAME -U USERNAME --file=20130212_backup.sql

4. Change table and data.

   Shell

   psql DBNAME -h HOSTNAME -U USERNAME -f 20130212_schema.sql psql DBNAME -h HOSTNAME -U USERNAME -f 20130212_data.sql

   1 2	psql DBNAME -h HOSTNAME -U USERNAME -f 20130212_schema.sql psql DBNAME -h HOSTNAME -U USERNAME -f 20130212_data.sql