Herramientas de usuario
wiki2:postgresql
Diferencias
Muestra las diferencias entre dos versiones de la página.
| Ambos lados, revisión anterior Revisión previa Próxima revisión | Revisión previa | ||
|
wiki2:postgresql [2018/09/17 10:42] alfred [Tips and Tricks] |
wiki2:postgresql [2020/05/09 09:25] (actual) |
||
|---|---|---|---|
| Línea 2: | Línea 2: | ||
| ===== Basics ===== | ===== Basics ===== | ||
| - | ''psql -U postgres'' enter into console with user postgres. | ||
| - | |||
| - | ''psql -U postgres -h 127.0.0.1'' for doing by TCP. | ||
| - | |||
| ''\q'' exit. | ''\q'' exit. | ||
| Línea 15: | Línea 11: | ||
| * ''\connect database_name'' to switch database | * ''\connect database_name'' to switch database | ||
| + | To access to a recently installed postgres server: | ||
| + | <code> | ||
| + | su - postgres | ||
| + | pgsql | ||
| + | </code> | ||
| + | |||
| + | To add a password: ''\password'' | ||
| + | |||
| + | ==== Command-line utils ==== | ||
| + | ''psql -U postgres'' enter into console with user postgres. | ||
| + | |||
| + | ''psql -U postgres -h 127.0.0.1'' for doing by TCP. | ||
| + | |||
| + | ''export PGPASSWORD=mysecretpassword && psql -h 127.0.0.1 -U postgres'' using a password. | ||
| + | |||
| + | <code> | ||
| + | $ export PGPASSWORD=mysecretpassword && dropdb -h 127.0.0.1 -U postgres fok | ||
| + | $ export PGPASSWORD=mysecretpassword && createdb -T template0 -h 127.0.0.1 -U postgres fok | ||
| + | </code> | ||
| ==== Create schema ==== | ==== Create schema ==== | ||
| <code> | <code> | ||
| Línea 59: | Línea 74: | ||
| </code> | </code> | ||
| + | ===== JSONB ===== | ||
| + | You can create a table with json data: | ||
| + | <code> | ||
| + | create table macarrodes.bookmarks ( | ||
| + | id serial primary key, | ||
| + | title text, | ||
| + | meta jsonb | ||
| + | ); | ||
| + | </code> | ||
| + | |||
| + | ==== Query ==== | ||
| + | Simple query showing a field: | ||
| + | <code> | ||
| + | select distinct meta->'tags' from macarrodes.macarrodes.bookmarks; | ||
| + | </code> | ||
| + | |||
| + | Query by field: | ||
| + | <code> | ||
| + | SELECT * FROM users WHERE metadata @> '{"country": "Peru"}'; | ||
| + | SELECT * FROM users WHERE metadata->>'country' = 'Peru'; | ||
| + | </code> | ||
| + | |||
| + | Query nested values: | ||
| + | <code> | ||
| + | SELECT * FROM users WHERE metadata->'company'->>'name' = "Mozilla"; | ||
| + | SELECT * FROM users WHERE metadata @> '{"company":{"name": "Mozilla"}}'; | ||
| + | </code> | ||
| + | |||
| + | Query if exists: | ||
| + | <code> | ||
| + | SELECT * FROM users WHERE metadata->>'country' IS NOT NULL; | ||
| + | </code> | ||
| + | |||
| + | Query in array: | ||
| + | <code> | ||
| + | SELECT * FROM users WHERE metadata @> '{"companies": ["Mozilla"]}'; | ||
| + | SELECT * FROM users WHERE metadata->>'countries' IN ('Chad', 'Japan'); | ||
| + | </code> | ||
| + | |||
| + | Remove an attribute: | ||
| + | <code> | ||
| + | UPDATE users SET metadata = metadata - 'country'; | ||
| + | </code> | ||
| ===== Funciones ===== | ===== Funciones ===== | ||
| Línea 139: | Línea 197: | ||
| ===== Notes ===== | ===== Notes ===== | ||
| + | |||
| + | ==== Backups and tools ==== | ||
| + | |||
| + | There is a command to create a DB: | ||
| + | <code> | ||
| + | createdb -T template0 restored_database | ||
| + | </code> | ||
| + | |||
| + | To create a backup: | ||
| + | <code> | ||
| + | pg_dump postgres > postgres_db.bak | ||
| + | </code> | ||
| + | |||
| + | To restore a backup: | ||
| + | <code> | ||
| + | createdb -T template0 restored_database | ||
| + | psql restored_database < database.bak | ||
| + | </code> | ||
| + | |||
| + | Backup and restore all DB: | ||
| + | <code> | ||
| + | pg_dumpall > backup_file | ||
| + | </code> | ||
| + | You can restore the databases by passing the file to psql, with the default database: | ||
| + | <code> | ||
| + | psql -f backup_file postgres | ||
| + | </code> | ||
| ==== Tips and Tricks ==== | ==== Tips and Tricks ==== | ||
| Script for restart and adding data using docker. | Script for restart and adding data using docker. | ||
| Línea 150: | Línea 235: | ||
| </code> | </code> | ||
| + | === Launch a command on CLI === | ||
| + | <code> | ||
| + | export PGPASSWORD=mysecretpassword && psql -h 127.0.0.1 -p {db.local_port} -U postgres -c 'create database puma;' | ||
| + | </code> | ||
| ==== Instalar servidor ==== | ==== Instalar servidor ==== | ||
| <code> | <code> | ||
wiki2/postgresql.1537180948.txt.gz · Última modificación: 2020/05/09 09:25 (editor externo)
Herramientas de la página
Excepto donde se indique lo contrario, el contenido de este wiki esta bajo la siguiente licencia: CC Attribution-Noncommercial-Share Alike 4.0 International
