Latest 100 public snipts »
Fotinakis's
snipts » postgresql 
showing 1-4 of 4 snipts for postgresql
-
∞ Output PostgreSQL command as CSV
psql -F\; -A --pset footer -f input_file.sql -o output_file.csv database_name -
∞ 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
-
∞ 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
-
∞ 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";