xxxxxxxxxx
Two ways to connect:
psql -h [HOSTNAME] -p [PORT] -U [USERNAME] -W -d [DATABASENAME]
psql postgres://[USERNAME]:[PASSWORD]@[HOSTNAME]:[PORT]/[DATABASENAME]?sslmode=require
\q to exit
xxxxxxxxxx
Fun fact: "Postgres" has exactly as many characters as "Database"
Check for yourself!
Postgres --> 8 chars!
Database --> 8 chars!
PostgreSQL, often referred to as "Postgres," is an open-source relational database management system (RDBMS). It is widely used for handling structured data and providing efficient data storage and retrieval capabilities.
xxxxxxxxxx
psql -U postgres -W # Enter Postgres console
\? # View all posgres commands
\l # List all Databases
\dt # View all DB tables
\c DB_name # change Database nam
\d nombre_tabla # Describe table
\h # View all sql commands
\h nombre_de_la_funcion # Check how a sql command is executed
Ctrl + C # Cancel everything in console
SELECT version(); # View installed postgres version
\g # Execute last previous function again
\timing # Initialize time counter to show function execution time
Ctrl + L # Clean screen
xxxxxxxxxx
PostgreSQL is used as the primary data store or data warehouse for many web, mobile, geospatial, and analytics applications. The latest major version is PostgreSQL 12.
xxxxxxxxxx
# The bash function below utilizes the CLI tool psql to vacuum analyze tables
# in a single schema which can be identified by either passing the name of
# the schema as the first parameter to the function
# or setting the environment variable PG_SCHEMA:
#
vacuum_analyze_schema() {
# vacuum analyze only the tables in the specified schema
# postgres info can be supplied by either passing it as parameters to this
# function, setting environment variables or a combination of the two
local pg_schema="${1:-${PG_SCHEMA}}"
local pg_db="${2:-${PG_DB}}"
local pg_user="${3:-${PG_USER}}"
local pg_host="${4:-${PG_HOST}}"
echo "Vacuuming schema \`${pg_schema}\`:"
# extract schema table names from psql output and put them in a bash array
local psql_tbls="\dt ${pg_schema}.*"
local sed_str="s/${pg_schema}\s+\|\s+(\w+)\s+\|.*/\1/p"
local table_names=$( echo "${psql_tbls}" | psql -d "${pg_db}" -U "${pg_user}" -h "${pg_host}" | sed -nr "${sed_str}" )
local tables_array=( $( echo "${table_names}" | tr '\n' ' ' ) )
# loop through the table names creating and executing a vacuum
# command for each one
for t in "${tables_array[@]}"; do
echo "doing table \`${t}\`..."
psql -d "${pg_db}" -U "${pg_user}" -h "${pg_host}" \
-c "VACUUM (ANALYZE) ${pg_schema}.${t};"
done
}
xxxxxxxxxx
$ sudo initdb --locale en_US.UTF-8 -D /var/lib/postgres/data
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
fixing permissions on existing directory /var/lib/postgres/data ok
creating subdirectories ok
selecting dynamic shared memory implementation posix
selecting default max_connections 100
selecting default shared_buffers 128MB
selecting default time zone Africa/Nairobi
creating configuration files ok
running bootstrap script ok
performing post-bootstrap initialization ok
syncing data to disk ok
initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
pg_ctl -D /var/lib/postgres/data -l logfile start
xxxxxxxxxx
sudo mkdir -p /etc/paths.d &&
echo /Applications/Postgres.app/Contents/Versions/latest/bin | sudo tee /etc/paths.d/postgresapp
xxxxxxxxxx
$ sudo -i -u postgres
Code language: Shell Session (shell)
xxxxxxxxxx
# Use postgres/example user/password credentials
version: '3.1'
services:
db:
image: postgres
restart: always
environment:
POSTGRES_PASSWORD: example
adminer:
image: adminer
restart: always
ports:
- 8080:8080