Backup and Restore PostgreSQL database - Windows

on

To Backup the database from PostgreSQL using pg_dump command with Windows.

  1. Navigate to PostgreSQL installation folder
    • Ex: C:\Program Files\PostgreSQL\10\bin
  2. Open Command Prompt window from PostgrSQL folder
  3. Use below command to take back of entire database
    • pg_dump -h hostname -p portnumber -U user -d dbname > file_location_to_save
    • Ex: pg_dump -h localhost -p 5432 -U dbadmin -d student  > D:\db_Backup.sql
    • Here dbadmin is User. student is database name
  4. Once hit enter button, it will ask password of user mentioned in the command.
  5. Type the password and Press Enter.
  6. SQL file will be generated in mentioned location.
To Backup single table from PostgreSQL using pg_dump command with Windows.

      • pg_dump -h hostname -p portnumber -U user -d dbname -t "tablename" >  file_lcoation_to_save
      • Ex: pg_dump -h localhost -p 5432 -U dbadmin -d student  -t "certificate" > D:\db_table_Backup.sql 
      • Here certificate is table available in student database
    Note: if database or table name with combination of small and capital letter then user as following,
    • Ex: pg_dump -h localhost -p 5432 -U dbadmin -d "\"Stuudent"\"  -t "\"Certificate"\" > D:\db_table_Backup.sql 

    POSTGRESQL RESTORE:

    To Restore the database from PostgreSQL using psql command with Windows.
      • psql -h hostname -p portnumber -U user -d dbname < file_location_to_restore
      • Ex: psql -h localhost -p 5432  -U dbadmin -d student  < D:\db_Backup.sql
    To Restore single table from PostgreSQL using psql command with Windows.
      • psql -h hostname -p portnumber  -U user -d dbname  -t "tablename" < file_location_to_restore
      • Ex: psql -h localhost -p 5432  -U dbadmin -d student  -t "certificate" < D:\db_Backup.sql

    Comments

    Post a Comment