Skip to content

Instantly share code, notes, and snippets.

@bienvenidosaez

bienvenidosaez/Utiles Postgres

Last active November 12, 2015 11:40
Show Gist options
  • Save bienvenidosaez/c09e5f28d28ec38cf999 to your computer and use it in GitHub Desktop.
Save bienvenidosaez/c09e5f28d28ec38cf999 to your computer and use it in GitHub Desktop.
Download ZIP
Postgres con Django
Raw
change_own.sh
#!/bin/bash
#
# The MIT License
#
# Copyright 2014 Jakub Jirutka <jakub@jirutka.cz>.
#
# Permission is hereby granted, free of charge, to any person obtaining a copy
# of this software and associated documentation files (the "Software"), to deal
# in the Software without restriction, including without limitation the rights
# to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
# copies of the Software, and to permit persons to whom the Software is
# furnished to do so, subject to the following conditions:
#
# The above copyright notice and this permission notice shall be included in
# all copies or substantial portions of the Software.
#
# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
# IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
# FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
# AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
# LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
# OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
# THE SOFTWARE.
# Credit: Based on http://stackoverflow.com/a/2686185/305019 by Alex Soto
usage() {
cat <<- EOF
usage: $0 options
This script changes ownership for all tables, views, sequences and functions in
a database schema and also owner of the schema itself.
Note: If you want to change the ownership of all objects, in the specified database,
owned by a database role, then you can simply use command "REASSIGN OWNED".
OPTIONS:
-h Show this message
-d Database name
-o New owner name
-s Schema (defaults to public)
EOF
}
pgexec() {
local cmd=$1
psql --no-psqlrc --no-align --tuples-only --record-separator=\0 --quiet \
--command="$cmd" "$DB_NAME"
}
pgexec_echo() {
local cmd=$1
psql --no-psqlrc --no-align --tuples-only --record-separator=\0 --quiet \
--echo-queries --command="$cmd" "$DB_NAME"
}
DB_NAME=''
NEW_OWNER=''
SCHEMA='public'
while getopts "hd:o:s:" OPTION; do
case $OPTION in
h)
usage
exit 1
;;
d)
DB_NAME=$OPTARG
;;
o)
NEW_OWNER=$OPTARG
;;
s)
SCHEMA=$OPTARG
;;
esac
done
if [[ -z "$DB_NAME" ]] || [[ -z "$NEW_OWNER" ]]; then
usage
exit 1
fi
# Using the NULL byte as the separator as its the only character disallowed from PG table names.
IFS=\0
# Change owner of schema itself.
pgexec_echo "ALTER SCHEMA \"${SCHEMA}\" OWNER TO \"${NEW_OWNER}\";"
# Change owner of tables and views.
for tbl in $(pgexec "SELECT table_name FROM information_schema.tables WHERE table_schema = '${SCHEMA}';") \
$(pgexec "SELECT table_name FROM information_schema.views WHERE table_schema = '${SCHEMA}';"); do
pgexec_echo "ALTER TABLE \"${SCHEMA}\".\"${tbl}\" OWNER TO ${NEW_OWNER};"
done
# Change owner of sequences.
for seq in $(pgexec "SELECT sequence_name FROM information_schema.sequences WHERE sequence_schema = '${SCHEMA}';"); do
pgexec_echo "ALTER SEQUENCE \"${SCHEMA}\".\"${seq}\" OWNER TO ${NEW_OWNER};"
done
# Change owner of functions and procedures.
for func in $(pgexec "SELECT quote_ident(p.proname) || '(' || pg_catalog.pg_get_function_identity_arguments(p.oid) || ')' \
FROM pg_catalog.pg_proc p JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace \
WHERE n.nspname = '${SCHEMA}';"); do
pgexec_echo "ALTER FUNCTION \"${SCHEMA}\".${func} OWNER TO ${NEW_OWNER};"
done
# Revert separator back to default.
unset IFS
Raw
Utiles Postgres
# Útiles para el uso de Postgres con Django
#Instalación en Ubuntu, sin tener activado entornos virtuales
sudo apt-get update
sudo apt-get install libpq-dev python-dev
sudo apt-get install postgresql postgresql-contrib
#Para cualquier uso de postgres cambiar al usuario postgres
sudo su - postgres
#Antes de hacer nada comprobar que el collation está en UTF8 para no tener problemas luego con la codificación de caracteres
#Podemos hacerlo manualmente o crear un script con el siguiente contenido y ejecutarlo
#Sacado de: https://gist.github.com/ffmike/877447 y https://gist.github.com/fgrehm/5436485
#Tablas de condificación de postgres: http://ingdesistemasvzla.blogspot.com.es/2011/02/cambiar-encoding-de-utf-8-latin1-en.html
if ! $(psql template1 -c 'SHOW SERVER_ENCODING' | grep -q UTF8); then
psql postgres -c "update pg_database set datallowconn = TRUE where datname = 'template0';"
psql template0 -c "update pg_database set datistemplate = FALSE where datname = 'template1';"
psql template0 -c "drop database template1;"
psql template0 -c "create database template1 with owner=postgres encoding='UTF-8' lc_collate='en_US.utf8' lc_ctype='en_US.utf8' template template0;"
psql template0 -c "create database template1 with template = template0 encoding = 'UTF8';"
psql template0 -c "update pg_database set datistemplate = TRUE where datname = 'template1';"
psql template1 -c "update pg_database set datallowconn = FALSE where datname = 'template0';"
fi
#Crear base de datos
createdb nombre_de_la_bd
#Crear usuario, no darle permisos para crear ni borrar bases de datos
createuser -P
#Dar privilegios a la base de datos al usuario creado y bd creada
psql
GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;
#Dar privilegios a un usuario concreto para usar la bd, tablas y sequencias
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO myuser;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO myuser;
#Comandos útiles
\q Salir
\l Listar las bases de datos
\du Listar los roles activos
ALTER USER Postgres WITH PASSWORD '<newpassword>';
#Para Django, estando el entorno virtual activado, instalar el driver para postgres
pip install psycopg2
#Configurar los settings
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.postgresql_psycopg2',
'NAME': 'mydb',
# The following settings are not used with sqlite3:
'USER': 'myuser',
'PASSWORD': 'password',
'HOST': 'localhost',
'PORT': '',
}
}
#Hacer un backup
# https://www.digitalocean.com/community/tutorials/how-to-backup-postgresql-databases-on-an-ubuntu-vps
sudo su - postgres
pg_dump postgres > postgres_db.bak
pg_dump -h remote_host -p remote_port name_of_database > name_of_backup_file
pg_dump -U user_name -h remote_host -p remote_port name_of_database > name_of_backup_file
#Backup sin permisos ni roles
pg_dump --no-privileges --no-owner --no-reconnect db > file.bck
pg_dump --no-privileges --no-owner --no-reconnect db > $( date '+%Y-%m-%d_%H-%M-%S' ).bck
#Restaurar
psql empty_database < backup_file
pg_restore --verbose --clean --no-acl --no-owner -h localhost -U myuser -d my_db db/latest.dump
#Si hemos hecho un empaqutado con pg_dump tenemos que restaurar con
pg_restore -d dbname filename
#Connectarse a una base de datos
\connect nombredelabasededatos
#Habilitar el acceso desde fuera
#Editar el archivo pg_hba.conf
sudo nano /etc/postgresql/9.3/main/pg_hba.conf
#Añadir una línea al final
host all all 0.0.0.0/0 md5
#Editar el archivo postgresql.conf
sudo nano /etc/postgresql/0.3/main/postgresql.conf
listen_addresses = '*'
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment