PostgreSQL - ghdrako/doc_snipets GitHub Wiki

PostgreSQL

SELECT version();

postgres is the PostgreSQL database server. In order for a client application to access a database it connects (over a network or locally) to a running postgres instance. The postgres instance then starts a separate server process to handle the connection. One postgres instance always manages the data of exactly one database cluster. A database cluster is a collection of databases that is stored at a common file system location (the “data area”).

When postgres starts it needs to know the location of the data area. The location must be specified by the -D option or the PGDATA environment variable; there is no default. Typically, -D or PGDATA points directly to the data area directory created by initdb.

Change default uiser from postgres to postgresuser: https://www.devonblog.com/security/changing-the-default-postgresql-user-identity/

https://micek1968.pl/?p=676

su - postgres
pg_env.sh   # ustawienie srodowiska
env | grep PG # sprawdzeie ustawienia srodowiska
ps -ef | grep Postgre # sprawdzenie czy dziala serwer
pg_ctl stop  # zatrzymanie servera
pg_ctl stop -m fast # jak nie dziala powyszy sposob
pg_ctl start -D /opt/PostgreSQL/9.2/data # start
service postgresql-9.2 stop #
service postgresql-9.2 start 
service postgresql-9.2 status
psql -l  # listę baz jakie są w naszej instancji
psql dvdrental # możemy zalogować się bezpośrednio do konkretnej bazy

\c moja_baza
\l  - lista baz
\l+
CREATE USER test_usr PASSWORD 'haslo123'
ALTER USER test_usr PASSWORD 'nowe_haslo123'
DROP USER IF EXIST test_usr
\dg   -- wyświetlić listę userów 
\dg+
CREATE TABLESPACE test_tbs OWNER test_usr LOCATION '/pgqdata/cdl/PostgreSQL/9.2/data';
CREATE DATABASE test_tab3 WITH OWNER = test_usr ENCODING = 'UTF8' TABLESPACE = test_tbs LC_COLLATE = 'pl_PL.utf8' LC_CTYPE = 'pl_PL.utf8' CONNECTION LIMIT = -1;
DROP DATABASE IF EXISTS test_baza
ALTER DATABASE name RENAME TO new_name
\c test_baza
ALTER DATABASE moja_baza OWNER TO postgres;
ALTER DATABASE test_baza SET TABLESPACE test_tbs;
CREATE SCHEMA test_schema
\dn -- list schema
\dn+
DROP SCHEMA IF EXISTS schema_name;
CREATE TABLE test_schema.test_tab1 (kolumna1 varchar(20));
\dt
\dt test_schema
CREATE TABLE test_tab2 (kolumna1 varchar(20)); -- create table in public schema
ALTER TABLE test_schema.test_tab RENAME TO test_tab1;
DROP TABLE IF EXISTS test_tab;
\dt *.

SELECT table_schema,table_name FROM information_schema.tables where not table_schema = 'pg_catalog'
ORDER BY table_schema,table_name;

\dt+ test_schema.test_tab  -- rozmiar tabeli
\q

Zmiana locals bazy danych

pg_dumpall > backup/all.sql
cp $PGDATA/*.conf backup/
pg_ctl stop
rm -rf $PGDATA
initdb --locale=pl_PL ( pod win32 locale może się nazywać inaczej )
cp backup/*.conf $PGDATA/
pg_ctl start
psql < backup/all.sql


initdb --lc-collate=pl_PL.UTF-8 --lc-ctype=pl_PL.UTF-8 --encoding=UTF-8 -D $PGDATA`
 pg_controldata .

pg_ctl stop

  • -m option allows you to select the shutdown method: smart, fast (default), or immediate.
  • fast and immediate force disconnection without waiting for the client to disconnect.
  • The "immediate" mode immediately interrupts all server processes without any cleanup action, so the crash recovery cycle starts the next time the server starts. Changes to the PostgreSQL configuration file will take effect the next time the server starts, regardless of mode.
  • -t option allows you to specify the number of seconds before the 'pg_ctl stop' operation