Public snipts » Fotinakis's snipts » postgresql

wrap codeloading...

• ∞ Output PostgreSQL command as CSV

  psql -F\; -A --pset footer -f input_file.sql -o output_file.csv database_name

  bash

  1

  Output PostgreSQL command as CSV

  psql -F\; -A --pset footer -f input_file.sql -o output_file.csv database_name
  

  copy | embed

  0 comments - tagged in 
  • csv, 
  • postgresql, 
  • psql
  posted by Fotinakis on Nov 10, 2009 at 4:04 p.m. EST

• ∞ Make copy and transfer entire PostgreSQL DB

  pg_dump -d prod_db -U prod_db -h localhost > /tmp/transferdump.sql
  
  psql -U prod_db_user -h localhost -t -d other_db -c "SELECT 'DROP TABLE ' || n.nspname || '.' ||
  c.relname || ' CASCADE;' FROM pg_catalog.pg_class AS c LEFT JOIN
  pg_catalog.pg_namespace AS n ON n.oid = c.relnamespace WHERE relkind =
  'r' AND n.nspname NOT IN ('pg_catalog', 'pg_toast') AND
  pg_catalog.pg_table_is_visible(c.oid)" >/tmp/dropothertables.sql;
  
  psql -d other_db -h localhost -U other_db_user < /tmp/dropothertables.sql;
  psql -d other_db -h localhost -U other_db_user < /tmp/transferdump.sql

  bash

  1

  Make copy and transfer entire PostgreSQL DB

  pg_dump -d prod_db -U prod_db -h localhost > /tmp/transferdump.sql
  
  psql -U prod_db_user -h localhost -t -d other_db -c "SELECT 'DROP TABLE ' || n.nspname || '.' ||
  c.relname || ' CASCADE;' FROM pg_catalog.pg_class AS c LEFT JOIN
  pg_catalog.pg_namespace AS n ON n.oid = c.relnamespace WHERE relkind =
  'r' AND n.nspname NOT IN ('pg_catalog', 'pg_toast') AND
  pg_catalog.pg_table_is_visible(c.oid)" >/tmp/dropothertables.sql;
  
  psql -d other_db -h localhost -U other_db_user < /tmp/dropothertables.sql;
  psql -d other_db -h localhost -U other_db_user < /tmp/transferdump.sql
  

  copy | embed

  0 comments - tagged in 
  • bash, 
  • pgdump, 
  • postgresql
  posted by Fotinakis on Jul 28, 2009 at 6:15 p.m. EDT

• ∞ Drop all tables from PostgreSQL DB without superuser

  psql -t -d my_dbname -c "SELECT 'DROP TABLE ' || n.nspname || '.' ||
  c.relname || ' CASCADE;' FROM pg_catalog.pg_class AS c LEFT JOIN
  pg_catalog.pg_namespace AS n ON n.oid = c.relnamespace WHERE relkind =
  'r' AND n.nspname NOT IN ('pg_catalog', 'pg_toast') AND
  pg_catalog.pg_table_is_visible(c.oid)" >/tmp/droptables
  
  psql -d my_dbname -f /tmp/droptables

  bash

  1

  Drop all tables from PostgreSQL DB without superuser

  psql -t -d my_dbname -c "SELECT 'DROP TABLE ' || n.nspname || '.' ||
  c.relname || ' CASCADE;' FROM pg_catalog.pg_class AS c LEFT JOIN
  pg_catalog.pg_namespace AS n ON n.oid = c.relnamespace WHERE relkind =
  'r' AND n.nspname NOT IN ('pg_catalog', 'pg_toast') AND
  pg_catalog.pg_table_is_visible(c.oid)" >/tmp/droptables
  
  psql -d my_dbname -f /tmp/droptables
  

  copy | embed

  0 comments - tagged in 
  • bash, 
  • db, 
  • postgresql
  posted by Fotinakis on Apr 28, 2009 at 12:09 p.m. EDT

• ∞ Grant all permissions on PostgreSQL DB

  ER="$2"
  PSQL="psql -q -n -A -t"
  SCHEMES="'public'"
  
  if [ -z "$1" ]; then
          echo "Something like: ./grant mydatabase myuser | psql mydatabase"
          exit
  fi
  
  if [ -z "$2" ]; then
          USER="$1"
  fi
  echo "-- Granting rights on $DB to $USER ($SCHEMES)"
  # tables
  Q="select 'grant all on '||schemaname||'.'||tablename||' to \\\"$USER\\\";' from pg_tables where schemaname in ($SCHEMES);"
  $PSQL -c "$Q" "$DB";
  
  # views
  Q="select 'grant all on '||schemaname||'.'||viewname||' to \\\"$USER\\\";' from pg_views where schemaname in ($SCHEMES);"
  $PSQL -c "$Q" "$DB";
  
  # sequences
  Q="select 'grant all on function '||n.nspname||'.'||p.proname||'('||oidvectortypes(p.proargtypes)||') to \\\"$USER\\\";' from pg_proc p, pg_namespace n where n.oid = p.pronamespace and n.nspname in ($SCHEMES);"
  $PSQL -c "$Q" "$DB";
  
  # functions
  Q="select 'grant all on '||n.nspname||'.'||c.relname||' to \\\"$USER\\\";' from pg_class c, pg_namespace n where n.oid = c.relnamespace and c.relkind IN ('S') and n.nspname in ($SCHEMES);"
  $PSQL -c "$Q" "$DB";

  bash

  1

  Grant all permissions on PostgreSQL DB

  ER="$2"
  PSQL="psql -q -n -A -t"
  SCHEMES="'public'"
  
  if [ -z "$1" ]; then
          echo "Something like: ./grant mydatabase myuser | psql mydatabase"
          exit
  fi
  
  if [ -z "$2" ]; then
          USER="$1"
  fi
  echo "-- Granting rights on $DB to $USER ($SCHEMES)"
  # tables
  Q="select 'grant all on '||schemaname||'.'||tablename||' to \\\"$USER\\\";' from pg_tables where schemaname in ($SCHEMES);"
  $PSQL -c "$Q" "$DB";
  
  # views
  Q="select 'grant all on '||schemaname||'.'||viewname||' to \\\"$USER\\\";' from pg_views where schemaname in ($SCHEMES);"
  $PSQL -c "$Q" "$DB";
  
  # sequences
  Q="select 'grant all on function '||n.nspname||'.'||p.proname||'('||oidvectortypes(p.proargtypes)||') to \\\"$USER\\\";' from pg_proc p, pg_namespace n where n.oid = p.pronamespace and n.nspname in ($SCHEMES);"
  $PSQL -c "$Q" "$DB";
  
  # functions
  Q="select 'grant all on '||n.nspname||'.'||c.relname||' to \\\"$USER\\\";' from pg_class c, pg_namespace n where n.oid = c.relnamespace and c.relkind IN ('S') and n.nspname in ($SCHEMES);"
  $PSQL -c "$Q" "$DB";
  

  copy | embed

  0 comments - tagged in 
  • db, 
  • grant, 
  • permissions, 
  • postgresql, 
  • psql
  posted by Fotinakis on Apr 28, 2009 at 12:05 p.m. EDT

A good read

Fotinakis's tags

• active directory (2)
• admin (3)
• apache (4)
• bash (5)
• bytecode (1)
• c++ (1)
• csv (1)
• db (2)
• debian (2)
• disassembler (1)
• django (5)
• drop (1)
• grant (1)
• history (1)
• hook (1)
• htaccess (1)
• javascript (1)
• jquery (1)
• ldap (2)
• linux (3)
• mac (1)
• macaddr (1)
• modwsgi (3)
• networking (1)
• oob (1)
• oracle (1)
• osx (1)
• oui (1)
• parsing (1)
• password (1)
• passwords (1)
• permissions (2)
• pgdump (1)
• postgresql (4)
• profiling (1)
• psql (2)
• python (4)
• random (1)
• security (2)
• server (1)
• settings (1)
• sql (1)
• ssh (2)
• sshfs (1)
• state (1)
• strings (1)
• template tag (1)
• tokenize (1)
• vmware (1)