Skip to content

Instantly share code, notes, and snippets.

@carpincho

carpincho/script.sh

Forked from vielhuber/script.sh
Created September 24, 2019 16:39
Show Gist options
  • Save carpincho/9ae5e4f72f4e66a38358e16a965222b1 to your computer and use it in GitHub Desktop.
Save carpincho/9ae5e4f72f4e66a38358e16a965222b1 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. @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;"
  2. @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
  3. @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
  4. @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;"
  5. @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
  6. @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
  7. @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

  8. @vielhuber vielhuber revised this gist Dec 10, 2016. No changes.
  9. @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
  10. @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
  11. @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
  12. @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.
  13. @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