PostgreSQL

-
1. PostgresSQL Overview

a) Database and RDBMS Introduction

- Database - " A database is an organized collection of data, generally stored  and access electronically from a computer system."

-RDBMS - A relational database follows the relational model proposed by E.f. Codd in 1970. It's a collection of data organized into tables, columns, and rows. These tables have well-defined relationships. A relational database management system (RDBMS) is software that enables us to interact with and manage relational databases

RDBM Server = Instance + Database

Instance = Memory + Processes

Database = Disk ( Data, Logs, Config)


Primary Key

Foreign Key 










b) PostgreSQL History

- 1986 - Micheal Stonebraker leads the University of California at Berkeley POSTGRES project.
- 1994 - An SQL language interpreter is added , and Postgres95 is released to the web.
- 1996 - The name is change to "PostgreSQL" and new features are added by the community over time.


c) PostgreSQL Architecture






d) Clustering Discussion 




e) Clients

- pgAdmin
- DBeaver
- PSQL
- phpPgAdmin
- Azure Data Studio


2.  Installation and Setup

a) Centos Installation

(1) Configure YUM Repository

sudo vi /etc/yum.repos.d/CentOS-base.repo
exclude=postgresql*

(2) Install PGDG RPM file

sudo yum - y install <URL>

(3) Install PostgreSQL

sudo yum install -y postgresql12-server \ postgresql12-contrib

(4) Initialize the Database

sudo /usr/ pgsql-12/bin/postgresql-12-setup \ 
initdb

(5) Start and Enable Service 

sudo systemctl  start postgresql-12
sudo systemctl enable postgresql-12

(6) Launch psql as postgres

sudo su - postgres
psql

(7) Secure postgres Role

\password postgres


b) Ubuntu Installation

(1) Configure YUM Repository

sudo vi /etc/apt/source.list.d/pgdg.list
deb http://apt.postgresql.org/pub/repos/apt/bionic-pgdg main

(2) Import the GPG Key

wget --quiet -o - <URL> | sudo apt-key add -

(3) Update Package List

sudo apt-get update

(4) Install PostgreSQL

sudo apt install postgresql postgresql-contrib

(5) Verify Service 

service postgresql status

(6) Launch psql as postgres

sudo su - postgres
psql

(7) Secure postgres Role

\password postgres

c) Server Configuration 

- postgresql.conf - Primary location for global instance settings

# - Memory 

shared_buffers = 128MB

#huge_pages =. try

#tmp_buffers = 8MB

#max_prepared_transaction = 0 

- SQL  - Set runtime parameters on live system

SET TIME ZONE 'Europe/Rome';
SHOW TIME ZONE;

- pg_hba.conf - Client authentication

# Allow replication connections from localhost , by a user with the replication privilege.
local   replication     all                               peer
local   replication     all         127.0.0.1/32          ident
local   replication     all         ::1/128               ident
local   all             all         0.0.0.0/0             mdS

d) Authentication and Authorization 

-Authentication Methods 

    *Trust
    *Password
    *GSSAPI
    *SSPI
    *Ident
    *Peer
    *LDAP
    *RADIUS
    *Certificate
    *PAM    
    *BSD

-Authorization

e) Database Configuration

- ALTER database

ALERT DATABASE name RENAME TO new_name
ALERT DATABASE name OWNER TO new_owner
ALERT DATABASE name SET TABLESPACE new_tablespace
ALERT DATABASE name SET configuration_parameter FROm CURRENT
ALERT DATABASE name RESET configuration_parameter
ALERT DATABASE name RESET ALL


3. Maintenance

a) Vacuuming

-Processes

    * Disk Space : Recover or reuse disk space
    * Statistics: Update statistics with ANALYZE
    * Visibility Map: Track visible or frozen pages , and increase index-only scans.
    * Transaction and Multixact ID's : Prevent failure from wraparound

    * VACUUM: runs faster and in parallel with production database operations
    * VACUUM FULL: Writes a new version of the table and therefore requiers an exclusive lock on it

- Types

b) Indexes

REINDEX [ ( VERBOSE ) ] (INDEX | TABLE | SCHEMA | DATABASE | SYSTEM ) [ CONCURRENTLY ] name

c) Backup and Recovery

- SQL Dump: Extract database to a script or archive
    * Restore anywhere
    * Simple and flexible
    * Current state only 
    * Large files
- File System Level Backup: Directly copy the files used  to store data
    * Faster
    * Entire cluster
    * No lock connection
    * Near-instant recovery
    * Requieres shutdown 
    * Version dependent 
    * All or nothing 
    * includes bloat
-Continuous Archiving / PITR: File System + WAL
    * No snapshot needed
    * Continuos backups
    * Point-in-time
    * Creates warm standby
    * Most complicated
    * Version dependent
    * All or nothing
    * Large archive storage

d) Troubleshooting

- System Catalogs
    * "The system catalogs are the place where a relational database management system stores schema metadata, such as information about tables and columns, and internal bookkeeping infromation. " - PostgreSQL documentation
    * Database information
    * Table information
    * Connection information 
    * Performance information


- Using pgAdmin
    * Real-Time Information
    * Quick Look at Server Activity
    * Access Statistics and Properties



4. Working with Data 

a) Creating Objects



b) Changing Data 
c) Selecting Data
d) Programming

5.

6. 

7.



http://suite.opengeo.org/docs/latest/dataadmin/pgGettingStarted/firstconnect.html

https://www.a2hosting.com/kb/developer-corner/postgresql/connect-to-postgresql-from-the-command-line

Docker for PostgreSQL

https://medium.com/@lvthillo/connect-from-local-machine-to-postgresql-docker-container-f785f00461a7



psql postgres -U postgres


/etc/postgresql/10/main/pg_hba.conf

deafult port 5432

sudo service postgresql restart

Postgres login commands

If you are logged into the same computer that Postgres is running on you can use the following psql login command, specifying the database (mydb) and username (myuser):
psql -d mydb -U myuser
If you need to log into a Postgres database on a server named myhost, you can use this Postgres login command:
psql -h myhost -d mydb -U myuser
If for some reason you are not prompted for a password when issuing these commands, you can use the -W option, leading to these two command alternatives:

psql -d mydb -U myuser -W
psql -h myhost -d mydb -U myuser -W


https://chartio.com/resources/tutorials/how-to-change-a-user-to-superuser-in-postgresql/

 http://www.postgresqltutorial.com/postgresql-hstore/

 https://www.postgresql.org/docs/9.0/static/user-manag.html

 https://www.digitalocean.com/community/tutorials/how-to-use-roles-and-manage-grant-permissions-in-postgresql-on-a-vps--2

Postgresql dump

 https://www.postgresql.org/docs/9.3/static/backup-dump.html


down vote

And... If in case you may not want to type a whole query... you can also type...
\l+ <database_name>
and you will get some details about the database, including the size of the database.
And... To get sizes of all databases.
you can just type...
\l+
You may need to go into the postgresql command prompt to query with these postgresql helper commands.
Check other postgresql helper commands by typing
\?
at the postgresql command prompt.

https://jdbc.postgresql.org/documentation/80/connect.html

 

/etc/init.d/postgresql restart




https://www.revsys.com/writings/postgresql/errors.html

Komentarze

Prześlij komentarz

Popularne posty z tego bloga

Kubernetes

-
Obraz
I. Introduction 1. Kubernetes Overview - Kubernetes = popular container orchestrator  * it lets you schedule containers on a cluster of machines  * you can run multiple containers on one machine  * you can run long running services (like web applications)  * Kubernetes will manage the state of those containers:    ** Can start the container on specific nodeskubectl create -f pod-demo.yml    ** Will restart a container when it gets killed    ** Can move containers from one dnode to another node - you can run Kubernetes anywhere:   *on-premise (own datacenter)   *public (Google cloud, AWS)   *hybrid: public & private -highly modular -open source -backed by google -Container Orchestration = Make many servers act like one -Released by Google in 2015, maintained by large community -Runs on top of Docker (usually) as a set of APIs in containers -Provides API/CLI to managed containers across server...
Czytaj więcej

Helm

-
 0. Overivew - Packet manager 1. Helm Provenance and Integrity - https://github.com/technosophos/k8s-helm/blob/master/docs/provenance.md - Helm has provenance tools which help chart users verify the integrity and origin of a package. Using industry-standard tools based on PKI, GnuPG, and well-respected package managers, Helm can generate and verify signature files. a) Overview   -  I ntegrity is established by comparing a chart to a provenance record. Provenance records are stored in   provenance files , which are stored alongside a packaged chart. For example, if a chart is named   myapp-1.2.3.tgz , its provenance file will be   myapp-1.2.3.tgz.prov .  -  Provenance files are generated at packaging time ( helm package --sign ... ), and can be checked by multiple commands, notable   helm install --verify . b) The workflow This section describes a potential workflow for using provenance data effectively. Prerequisites: A valid PGP keyp...
Czytaj więcej

Ansible Tower / AWX

-
- Ansible Tower provides a web server interface to ansible.  - System rewuierments are somewhat heavy.  - Tower is only free for minimal use. Working with more than a few system requires a paid lincense.  - The two keys benefots of Ansible Tower are user permissioning and the audit trail (only provided with license.  - Only touched on in EX4077  - How is ansible Tower installed on your system? Ansible Tower is provided in a tarball containing binaries, config files, and an installation script that must be ran.  - To populate an Ansible Tower project with source files can be used:        * file system of the Ansible Tower server        * git        * subversion  - features:        * an interface for running Ansible plays and playbooks against target hosts.       * Separate user accounts permissioned for selective access to Tower-managed ...
Czytaj więcej