How to import/export CSV data in/from PostgreSQL

In this blog, we are going to see -

How to export data from PostgreSQL tables to CSV files and
How to import data from  csv file into a PostgreSQL  table.

I have created  this table in PG -

Contents of CSV data file -

We can import CSV file into this 'my_table'  table using copy command  -

Table columns orders should be the same which is mentioned in CSV file or vice versa. 
Header keyword is indicating CSV file contain header i.e columns name
Delimiter is used to indicate value is separated using ','

Data has been imported into PG table -

Similarly ,We can perform Export PG table data into CSV file using copy command -

my_export_data.csv is being created

Instead of selecting all the columns , we can select any particular column as well ,in this example
using only id column

my_export_data.csv is showing data of column 'id'

We can also specify condition if any particular data we are looking -for instance id=1

More details can be  found at - COPY command

pg_upgrade - Easiest way to upgrade PostgreSQL cluster

In this blog, I am going to provide details steps to do upgrades a PostgreSQL cluster to a different major version (for instance PG v11 to PG v12) .
There are few  conditions which  need to be meet-
A)Both clusters should not be running (so little down time is required but this is the most efficient way) to upgrade PostgreSQL version from X major version to Y major version.
B)A new database cluster (using the new version of initdb) for  PostgreSQL version which we need to migrate.

These below are the available options of pg_upgrade utility -

Lets assume - PG v11 is the current server which contain all data/objects.

and after PG v12 release  which is the latest and the greatest contains new features including critical bug fixes -User wants to migrate.

Note- It is always recommended to do  testing on testing box  before touching the live server.

Prerequisites -
First need to make sure - PG v12 is installed on the same machine where PG v11 is installed/Running.
Shutdown the PG v11 server.

Steps to perform pg_upgrade -

Perform initdb ( initdb utility  can be found inside  bin/ directory of PG v12)

When we run pg_upgrade, we should  provide the following information:

 ->data directory for the old cluster  (-d old_data_directory_path)

 ->data directory for the new cluster  (-D new_data_directory_path)

 ->bin directory for the old version (-b old_bin_directory_path)

 ->bin directory for the new version (-B new_bin_directory_path)

For example:

  pg_upgrade -d <old_data_directory_path> -D <new_data_directory_path> -b  <old_bin_directory_path> -B <new_bin_directory_path>

Things to remember-

a)If somehow pg_upgrade get failed and we want to try it again then it is better to perform initdb again (PG v12) . Don't use the existing PG v12 cluster otherwise if we try to run pg_upgrade again without performing  fresh PG v12 - we can get some weird error like -

"

Setting frozenxid and minmxid counters in new cluster  connection to database failed: 

FATAL:  could not open relation with OID 2610

Failure, exiting

"

b)Always recommend to use dry run (option -c) first

 pg_upgrade -d data11/  -D  data12/  -b  /home/user/pg11/bin  -B /home/user/pg12/bin -c

if everything goes well, will get this message - 

*Clusters are compatible*

"./pg_ctl" -w -D "data12" -o "" -m smart stop >> "pg_upgrade_server.log" 2>&1

c)Feel free to report the issue to  pgsql-bugs@postgresql.org with log files.