Skip to content

Instantly share code, notes, and snippets.

@usmanfaisal

usmanfaisal/script.sh

Forked from vielhuber/script.sh
Created June 13, 2022 07:48
Show Gist options
  • Save usmanfaisal/518fff000d17727fb0213062979cf570 to your computer and use it in GitHub Desktop.
Save usmanfaisal/518fff000d17727fb0213062979cf570 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. @vielhuber vielhuber revised this gist Mar 12, 2021. No changes.
  2. @vielhuber vielhuber revised this gist Feb 12, 2021. 1 changed file with 2 additions and 2 deletions.
    4 changes: 2 additions & 2 deletions script.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -21,7 +21,7 @@ psql --set ON_ERROR_STOP=on -U postgres -d database -1 -f file.sql
    pg_restore --no-privileges --no-owner -U postgres -d database --clean file.sql # only works for special dumps

    # backup excluding table
    pg_dump --no-owner -h 127.0.0.1 -p 5432 -U username --exclude-table=table1 --exclude-table=table2 database > tmp.sql
    pg_dump --no-owner -h 127.0.0.1 -p 5432 -U username --exclude-table=table1 --exclude-table=table2 --exclude-table=table1_id_seq --exclude-table=table2_id_seq database > tmp.sql

    # backup including only table
    pg_dump --no-owner -h 127.0.0.1 -p 5432 -U username --table=table1 --table=table2 database > tmp.sql
    @@ -30,7 +30,7 @@ pg_dump --no-owner -h 127.0.0.1 -p 5432 -U username --table=table1 --table=table
    pg_dump --no-owner -h 127.0.0.1 -p 5432 -U username --schema-only database > tmp.sql

    # transfer database with 2 tables empty
    pg_dump --no-owner -h 127.0.0.1 -p 5432 -U username --exclude-table=table1 --exclude-table=table2 database > 1.sql
    pg_dump --no-owner -h 127.0.0.1 -p 5432 -U username --exclude-table=table1 --exclude-table=table2 --exclude-table=table1_id_seq --exclude-table=table2_id_seq database > 1.sql
    pg_dump --no-owner -h 127.0.0.1 -p 5432 -U username --table=table1 --table=table2 --schema-only database > 2.sql
    psql --set ON_ERROR_STOP=on -U username -d database -1 -f 1.sql
    psql --set ON_ERROR_STOP=on -U username -d database -1 -f 2.sql
  3. @vielhuber vielhuber revised this gist Feb 12, 2021. 1 changed file with 9 additions and 0 deletions.
    9 changes: 9 additions & 0 deletions script.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -26,6 +26,15 @@ pg_dump --no-owner -h 127.0.0.1 -p 5432 -U username --exclude-table=table1 --exc
    # backup including only table
    pg_dump --no-owner -h 127.0.0.1 -p 5432 -U username --table=table1 --table=table2 database > tmp.sql

    # backup only schema (no data)
    pg_dump --no-owner -h 127.0.0.1 -p 5432 -U username --schema-only database > tmp.sql

    # transfer database with 2 tables empty
    pg_dump --no-owner -h 127.0.0.1 -p 5432 -U username --exclude-table=table1 --exclude-table=table2 database > 1.sql
    pg_dump --no-owner -h 127.0.0.1 -p 5432 -U username --table=table1 --table=table2 --schema-only database > 2.sql
    psql --set ON_ERROR_STOP=on -U username -d database -1 -f 1.sql
    psql --set ON_ERROR_STOP=on -U username -d database -1 -f 2.sql

    # backup and restore
    PGPASSWORD=password && pg_dump --no-owner -h 127.0.0.1 -p 5432 -U username database > tmp.sql
    psql -U postgres -d database -c "drop schema public cascade; create schema public;"
  4. @vielhuber vielhuber revised this gist Feb 12, 2021. 1 changed file with 4 additions and 4 deletions.
    8 changes: 4 additions & 4 deletions script.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -20,11 +20,11 @@ pg_dump --no-owner --dbname=postgresql://username:password@host:port/database >
    psql --set ON_ERROR_STOP=on -U postgres -d database -1 -f file.sql
    pg_restore --no-privileges --no-owner -U postgres -d database --clean file.sql # only works for special dumps

    # backup exluding table
    pg_dump --no-owner -h 127.0.0.1 -p 5432 -U username --exclude-table=foo database > tmp.sql
    # backup excluding table
    pg_dump --no-owner -h 127.0.0.1 -p 5432 -U username --exclude-table=table1 --exclude-table=table2 database > tmp.sql

    # backup including table
    pg_dump --no-owner -h 127.0.0.1 -p 5432 -U username --table=foo database > tmp.sql
    # backup including only table
    pg_dump --no-owner -h 127.0.0.1 -p 5432 -U username --table=table1 --table=table2 database > tmp.sql

    # backup and restore
    PGPASSWORD=password && pg_dump --no-owner -h 127.0.0.1 -p 5432 -U username database > tmp.sql
  5. @vielhuber vielhuber revised this gist Jan 22, 2020. 1 changed file with 6 additions and 2 deletions.
    8 changes: 6 additions & 2 deletions script.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -17,7 +17,8 @@ PGPASSWORD=password&& pg_dump --no-owner -h host -p port -U username database >
    pg_dump --no-owner --dbname=postgresql://username:password@host:port/database > file.sql

    # restore
    psql --set ON_ERROR_STOP=on -U postgres database < file.sql
    psql --set ON_ERROR_STOP=on -U postgres -d database -1 -f file.sql
    pg_restore --no-privileges --no-owner -U postgres -d database --clean file.sql # only works for special dumps

    # backup exluding table
    pg_dump --no-owner -h 127.0.0.1 -p 5432 -U username --exclude-table=foo database > tmp.sql
    @@ -29,4 +30,7 @@ pg_dump --no-owner -h 127.0.0.1 -p 5432 -U username --table=foo database > tmp.s
    PGPASSWORD=password && pg_dump --no-owner -h 127.0.0.1 -p 5432 -U username database > tmp.sql
    psql -U postgres -d database -c "drop schema public cascade; create schema public;"
    psql --set ON_ERROR_STOP=on -U postgres -d database -1 -f tmp.sql
    rm tmp.sql
    rm tmp.sql

    # if you need to remove unintentionally backuped owners afterwards
    sed -i.bak '/OWNER TO specialowner/d' input.sql
  6. @vielhuber vielhuber revised this gist Nov 17, 2018. 1 changed file with 3 additions and 0 deletions.
    3 changes: 3 additions & 0 deletions script.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -22,6 +22,9 @@ psql --set ON_ERROR_STOP=on -U postgres database < file.sql
    # backup exluding table
    pg_dump --no-owner -h 127.0.0.1 -p 5432 -U username --exclude-table=foo database > tmp.sql

    # backup including table
    pg_dump --no-owner -h 127.0.0.1 -p 5432 -U username --table=foo database > tmp.sql

    # backup and restore
    PGPASSWORD=password && pg_dump --no-owner -h 127.0.0.1 -p 5432 -U username database > tmp.sql
    psql -U postgres -d database -c "drop schema public cascade; create schema public;"
  7. @vielhuber vielhuber revised this gist Nov 16, 2018. 1 changed file with 5 additions and 5 deletions.
    10 changes: 5 additions & 5 deletions script.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -8,22 +8,22 @@ edit %APPDATA%\postgresql\pgpass.conf
    *:5432:*:username:password

    # linux
    PGPASSWORD="password" pg_dump -h host -p port -U username database > file.sql
    PGPASSWORD="password" pg_dump --no-owner -h host -p port -U username database > file.sql

    # windows
    PGPASSWORD=password&& pg_dump -h host -p port -U username database > file.sql
    PGPASSWORD=password&& pg_dump --no-owner -h host -p port -U username database > file.sql

    # alternative
    pg_dump --dbname=postgresql://username:password@host:port/database > file.sql
    pg_dump --no-owner --dbname=postgresql://username:password@host:port/database > file.sql

    # restore
    psql --set ON_ERROR_STOP=on -U postgres database < file.sql

    # backup exluding table
    pg_dump -h 127.0.0.1 -p 5432 -U username --exclude-table=foo database > tmp.sql
    pg_dump --no-owner -h 127.0.0.1 -p 5432 -U username --exclude-table=foo database > tmp.sql

    # backup and restore
    PGPASSWORD=password && pg_dump -h 127.0.0.1 -p 5432 -U username database > tmp.sql
    PGPASSWORD=password && pg_dump --no-owner -h 127.0.0.1 -p 5432 -U username database > tmp.sql
    psql -U postgres -d database -c "drop schema public cascade; create schema public;"
    psql --set ON_ERROR_STOP=on -U postgres -d database -1 -f tmp.sql
    rm tmp.sql
  8. @vielhuber vielhuber revised this gist Nov 16, 2018. 1 changed file with 2 additions and 2 deletions.
    4 changes: 2 additions & 2 deletions script.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -17,13 +17,13 @@ PGPASSWORD=password&& pg_dump -h host -p port -U username database > file.sql
    pg_dump --dbname=postgresql://username:password@host:port/database > file.sql

    # restore
    psql -U postgres database < file.sql
    psql --set ON_ERROR_STOP=on -U postgres database < file.sql

    # backup exluding table
    pg_dump -h 127.0.0.1 -p 5432 -U username --exclude-table=foo database > tmp.sql

    # backup and restore
    PGPASSWORD=password && pg_dump -h 127.0.0.1 -p 5432 -U username database > tmp.sql
    psql -U postgres -d database -c "drop schema public cascade; create schema public;"
    psql -U postgres -d database -1 -f tmp.sql
    psql --set ON_ERROR_STOP=on -U postgres -d database -1 -f tmp.sql
    rm tmp.sql
  9. @vielhuber vielhuber revised this gist Sep 14, 2018. 1 changed file with 3 additions and 0 deletions.
    3 changes: 3 additions & 0 deletions script.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -19,6 +19,9 @@ pg_dump --dbname=postgresql://username:password@host:port/database > file.sql
    # restore
    psql -U postgres database < file.sql

    # backup exluding table
    pg_dump -h 127.0.0.1 -p 5432 -U username --exclude-table=foo database > tmp.sql

    # backup and restore
    PGPASSWORD=password && pg_dump -h 127.0.0.1 -p 5432 -U username database > tmp.sql
    psql -U postgres -d database -c "drop schema public cascade; create schema public;"
  10. @vielhuber vielhuber revised this gist Nov 17, 2017. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion script.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -20,7 +20,7 @@ pg_dump --dbname=postgresql://username:password@host:port/database > file.sql
    psql -U postgres database < file.sql

    # backup and restore
    pg_dump -h 127.0.0.1 -p 5432 -U username database > tmp.sql
    PGPASSWORD=password && pg_dump -h 127.0.0.1 -p 5432 -U username database > tmp.sql
    psql -U postgres -d database -c "drop schema public cascade; create schema public;"
    psql -U postgres -d database -1 -f tmp.sql
    rm tmp.sql
  11. @vielhuber vielhuber revised this gist Nov 17, 2017. 1 changed file with 7 additions and 1 deletion.
    8 changes: 7 additions & 1 deletion script.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -17,4 +17,10 @@ PGPASSWORD=password&& pg_dump -h host -p port -U username database > file.sql
    pg_dump --dbname=postgresql://username:password@host:port/database > file.sql

    # restore
    psql -U postgres database < file.sql
    psql -U postgres database < file.sql

    # backup and restore
    pg_dump -h 127.0.0.1 -p 5432 -U username database > tmp.sql
    psql -U postgres -d database -c "drop schema public cascade; create schema public;"
    psql -U postgres -d database -1 -f tmp.sql
    rm tmp.sql
  12. @vielhuber vielhuber revised this gist Nov 5, 2017. 1 changed file with 9 additions and 0 deletions.
    9 changes: 9 additions & 0 deletions script.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -1,3 +1,12 @@
    # best practice: linux
    nano ~/.pgpass
    *:5432:*:username:password
    chmod 0600 ~/.pgpass

    # best practice: windows
    edit %APPDATA%\postgresql\pgpass.conf
    *:5432:*:username:password

    # linux
    PGPASSWORD="password" pg_dump -h host -p port -U username database > file.sql

  13. @vielhuber vielhuber revised this gist Dec 10, 2016. No changes.
  14. @vielhuber vielhuber revised this gist Dec 9, 2016. 1 changed file with 4 additions and 1 deletion.
    5 changes: 4 additions & 1 deletion script.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -5,4 +5,7 @@ PGPASSWORD="password" pg_dump -h host -p port -U username database > file.sql
    PGPASSWORD=password&& pg_dump -h host -p port -U username database > file.sql

    # alternative
    pg_dump --dbname=postgresql://username:password@host:port/database > file.sql
    pg_dump --dbname=postgresql://username:password@host:port/database > file.sql

    # restore
    psql -U postgres database < file.sql
  15. @vielhuber vielhuber revised this gist Aug 15, 2016. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion script.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -1,4 +1,4 @@
    # first
    # linux
    PGPASSWORD="password" pg_dump -h host -p port -U username database > file.sql

    # windows
  16. @vielhuber vielhuber revised this gist Aug 15, 2016. 1 changed file with 3 additions and 0 deletions.
    3 changes: 3 additions & 0 deletions script.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -1,5 +1,8 @@
    # first
    PGPASSWORD="password" pg_dump -h host -p port -U username database > file.sql

    # windows
    PGPASSWORD=password&& pg_dump -h host -p port -U username database > file.sql

    # alternative
    pg_dump --dbname=postgresql://username:password@host:port/database > file.sql
  17. @vielhuber vielhuber renamed this gist Aug 15, 2016. 1 changed file with 0 additions and 0 deletions.
    0 script.sql → script.sh
    View file
    File renamed without changes.
  18. @vielhuber vielhuber created this gist Aug 15, 2016.
    5 changes: 5 additions & 0 deletions script.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,5 @@
    # first
    PGPASSWORD="password" pg_dump -h host -p port -U username database > file.sql

    # alternative
    pg_dump --dbname=postgresql://username:password@host:port/database > file.sql