Архив рубрики: PostgreSQL

SQL бекап gzip

Создание и восстановление заархивированного дампа SQL MySQL

mysqldump -u USER -p DATABASE | gzip > /path/to/outputfile.sql.gz
gunzip < /path/to/outputfile.sql.gz | mysql -h localhost -u USER -p DATABASE

1 2	mysqldump -u USER -p DATABASE \| gzip > /path/to/outputfile.sql.gz gunzip < /path/to/outputfile.sql.gz \| mysql -h localhost -u USER -p DATABASE

PostgreSQL

pg_dump DATABASE | gzip >/path/to/outputfile.sql.gz
gunzip < /path/to/outputfile.sql.gz | psql -h localhost DATABASE

1 2	pg_dump DATABASE \| gzip >/path/to/outputfile.sql.gz gunzip < /path/to/outputfile.sql.gz \| psql -h localhost DATABASE

PostgreSQL :: узнать размер таблиц базы данных

SELECT pg_size_pretty(pg_relation_size('my_table'));
SELECT pg_size_pretty(pg_total_relation_size('my_table'));
SELECT pg_size_pretty(pg_indexes_size('my_table'));
SELECT
    relname AS "relation",
    pg_size_pretty(pg_total_relation_size (C .oid)) AS "total_size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C .relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
  AND C .relkind <> 'i'
  AND nspname !~ '^pg_toast'
ORDER BY
    pg_total_relation_size(C.oid) DESC
LIMIT 5;
SELECT pg_size_pretty(pg_database_size ('my_database'));
SELECT
    pg_database.datname,
    pg_size_pretty(pg_database_size(pg_database.datname)) AS size
FROM pg_database;

SELECT pg_size_pretty(pg_relation_size('my_table'));

SELECT pg_size_pretty(pg_total_relation_size('my_table'));

SELECT pg_size_pretty(pg_indexes_size('my_table'));

SELECT

relname AS "relation",

pg_size_pretty(pg_total_relation_size (C .oid)) AS "total_size"

FROM pg_class C

LEFT JOIN pg_namespace N ON (N.oid = C .relnamespace)

WHERE nspname NOT IN ('pg_catalog', 'information_schema')

AND C .relkind <> 'i'

AND nspname !~ '^pg_toast'

ORDER BY

pg_total_relation_size(C.oid) DESC

LIMIT 5;

SELECT pg_size_pretty(pg_database_size ('my_database'));

SELECT

pg_database.datname,

pg_size_pretty(pg_database_size(pg_database.datname)) AS size

FROM pg_database;

Узнать размер всех таблиц базы данных можно с помощью такого запроса:

SELECT 
    relname AS name,
    relfilenode AS oid,
    (relpages::bigint * 8192 / (1024*1024))::bigint AS size_mb,
    reltuples AS cnt 
FROM pg_class 
WHERE relname IN (
    SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname = 'public'
) 
ORDER BY cnt DESC;

SELECT

relname AS name,

relfilenode AS oid,

(relpages::bigint * 8192 / (1024*1024))::bigint AS size_mb,

reltuples AS cnt

FROM pg_class

WHERE relname IN (

SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname = 'public'

)

ORDER BY cnt DESC;

Centos 7 apache nginx php-fpm git — установка

Пошаговая инструкция по установка окружения веб-разработчика на чистой VPS CentOS 7

cat /etc/redhat-release
yum update

# Общие пакеты
yum install sudo curl vim mc net-tools bind-utils mlocate gcc-c++

# Apache
yum install httpd
systemctl enable httpd

# PHP 5.6
rpm -Uvh https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
rpm -Uvh https://mirror.webtatic.com/yum/el7/webtatic-release.rpm
yum install php56w php56w-opcache php56w-xml php56w-mcrypt php56w-gd php56w-devel php56w-intl php56w-mbstring php56w-bcmath php56w-pdo php56w-mysql php56w-pgsql php56w-pear php56w-pecl-apcu php56w-fpm php56w-process
systemctl enable php-fpm

# Nginx
vim /etc/yum.repos.d/nginx.repo
    [nginx]
    name=nginx repo
    baseurl=https://nginx.org/packages/centos/$releasever/$basearch/
    gpgcheck=0
    enabled=1
yum install nginx
systemctl enable nginx

# GIT
wget https://github.com/git/git/archive/v2.6.3.tar.gz
tar -zxf v2.6.3.tar.gz
cd git-2.6.3/
yum install curl-devel expat-devel gettext-devel openssl-devel zlib-devel asciidoc xmlto docbook2x perl-devel docbook2X
ln -s /usr/bin/db2x_docbook2texi /usr/bin/docbook2x-texi
make configure
./configure --prefix=/usr
make all doc info
make install install-doc install-html install-info

# MySql 5.6
wget https://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm
rpm -ivh mysql-community-release-el7-5.noarch.rpm
yum install mysql-server
systemctl start mysqld
systemctl enable mysqld
mysql_secure_installation

# PostgreSQL 9.4
yum install https://yum.postgresql.org/9.4/redhat/rhel-6-x86_64/pgdg-redhat94-9.4-1.noarch.rpm
yum install postgresql94-server postgresql94-contrib
/usr/pgsql-9.4/bin/postgresql94-setup initdb
systemctl enable postgresql-9.4.service
systemctl start postgresql-9.4.service

cat /etc/redhat-release

yum update

# Общие пакеты

yum install sudo curl vim mc net-tools bind-utils mlocate gcc-c++

# Apache

yum install httpd

systemctl enable httpd

# PHP 5.6

rpm -Uvh https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm

rpm -Uvh https://mirror.webtatic.com/yum/el7/webtatic-release.rpm

yum install php56w php56w-opcache php56w-xml php56w-mcrypt php56w-gd php56w-devel php56w-intl php56w-mbstring php56w-bcmath php56w-pdo php56w-mysql php56w-pgsql php56w-pear php56w-pecl-apcu php56w-fpm php56w-process

systemctl enable php-fpm

# Nginx

vim /etc/yum.repos.d/nginx.repo

[nginx]

name=nginx repo

baseurl=https://nginx.org/packages/centos/$releasever/$basearch/

gpgcheck=0

enabled=1

yum install nginx

systemctl enable nginx

# GIT

wget https://github.com/git/git/archive/v2.6.3.tar.gz

tar -zxf v2.6.3.tar.gz

cd git-2.6.3/

yum install curl-devel expat-devel gettext-devel openssl-devel zlib-devel asciidoc xmlto docbook2x perl-devel docbook2X

ln -s /usr/bin/db2x_docbook2texi /usr/bin/docbook2x-texi

make configure

./configure --prefix=/usr

make all doc info

make install install-doc install-html install-info

# MySql 5.6

wget https://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm

rpm -ivh mysql-community-release-el7-5.noarch.rpm

yum install mysql-server

systemctl start mysqld

systemctl enable mysqld

mysql_secure_installation

# PostgreSQL 9.4

yum install https://yum.postgresql.org/9.4/redhat/rhel-6-x86_64/pgdg-redhat94-9.4-1.noarch.rpm

yum install postgresql94-server postgresql94-contrib

/usr/pgsql-9.4/bin/postgresql94-setup initdb

systemctl enable postgresql-9.4.service

systemctl start postgresql-9.4.service

PostgreSQL :: получить структуру полей любых таблиц

Предлагаю postgresql функцию, позволяющую получить атрибуты всех полей и ключей из любых таблиц сервера. Начну с примера. Допустим надо вывести атрибуты таблицы fos_user:

# select * from return_ro_tables_info() where tab_name='fos_user';

 tab_name | attnum |         field         |   type    | isnotnull | pri |           default_adsrc           |     comment
----------+--------+-----------------------+-----------+-----------+-----+-----------------------------------+-----------------
 fos_user |      1 | id                    | int8      | t         | t   |                                   |
 fos_user |      2 | username              | varchar   | t         |     |                                   |
 fos_user |      3 | username_canonical    | varchar   | t         |     |                                   |
 fos_user |      4 | email                 | varchar   | t         |     |                                   |
 fos_user |      5 | email_canonical       | varchar   | t         |     |                                   |
 fos_user |      6 | enabled               | bool      | t         |     |                                   |
 fos_user |      7 | salt                  | varchar   | t         |     |                                   |
 fos_user |      8 | password              | varchar   | t         |     |                                   |
 fos_user |      9 | last_login            | timestamp | f         |     | NULL::timestamp without time zone |
 fos_user |     10 | locked                | bool      | t         |     |                                   |
 fos_user |     11 | expired               | bool      | t         |     |                                   |
 fos_user |     12 | expires_at            | timestamp | f         |     | NULL::timestamp without time zone |
 fos_user |     13 | confirmation_token    | varchar   | f         |     | NULL::character varying           |
 fos_user |     14 | password_requested_at | timestamp | f         |     | NULL::timestamp without time zone |
 fos_user |     15 | roles                 | text      | t         |     |                                   | (DC2Type:array)
 fos_user |     16 | credentials_expired   | bool      | t         |     |                                   |
 fos_user |     17 | credentials_expire_at | timestamp | f         |     | NULL::timestamp without time zone |
(17 rows)

# select * from return_ro_tables_info() where tab_name='fos_user';

----------+--------+-----------------------+-----------+-----------+-----+-----------------------------------+-----------------

fos_user | 1 | id | int8 | t | t | |

(17 rows)

Названия колонок таблицы говорят сами за себя, описывать не буду Ну и, собственно, сам код функции

-- Информация о колонках и ключах таблиц
DROP FUNCTION IF EXISTS return_ro_tables_info();
CREATE OR REPLACE FUNCTION return_ro_tables_info() RETURNS TABLE(
    tab_name      varchar,
    attnum        integer,
    field         varchar,
    type          varchar,
    isnotnull     boolean,
    pri           boolean,
    default_adsrc varchar,
    comment       varchar
) AS
$$
    SELECT
        c.relname::varchar AS tab_name,
        a.attnum::integer,
        a.attname::varchar AS field,
        t.typname::varchar AS type,
        a.attnotnull::boolean AS isnotnull,
        (SELECT 't'::boolean
            FROM pg_index
            WHERE c.oid = pg_index.indrelid
            AND pg_index.indkey[0] = a.attnum
            AND pg_index.indisprimary = 't'
        ) AS pri,
        (SELECT pg_attrdef.adsrc::varchar
            FROM pg_attrdef
            WHERE c.oid = pg_attrdef.adrelid AND pg_attrdef.adnum=a.attnum
        ) AS default_adsrc,
        (SELECT pg_description.description::varchar
            FROM pg_description WHERE pg_description.objoid = c.oid AND a.attnum = pg_description.objsubid
        ) AS comment
    FROM pg_attribute a, pg_class c, pg_type t, pg_namespace n
    WHERE n.nspname = 'public'
        AND a.attnum > 0
        AND a.attrelid = c.oid
        AND a.atttypid = t.oid
        AND n.oid = c.relnamespace
    ORDER BY  c.relname, a.attnum;
$$
LANGUAGE sql;

-- Информация о колонках и ключах таблиц

DROP FUNCTION IF EXISTS return_ro_tables_info();

CREATE OR REPLACE FUNCTION return_ro_tables_info() RETURNS TABLE(

tab_name varchar,

attnum integer,

field varchar,

type varchar,

isnotnull boolean,

pri boolean,

default_adsrc varchar,

comment varchar

) AS

SELECT

c.relname::varchar AS tab_name,

a.attnum::integer,

a.attname::varchar AS field,

t.typname::varchar AS type,

a.attnotnull::boolean AS isnotnull,

(SELECT 't'::boolean

FROM pg_index

WHERE c.oid = pg_index.indrelid

AND pg_index.indkey[0] = a.attnum

AND pg_index.indisprimary = 't'

) AS pri,

(SELECT pg_attrdef.adsrc::varchar

FROM pg_attrdef

WHERE c.oid = pg_attrdef.adrelid AND pg_attrdef.adnum=a.attnum

) AS default_adsrc,

(SELECT pg_description.description::varchar

FROM pg_description WHERE pg_description.objoid = c.oid AND a.attnum = pg_description.objsubid

) AS comment

FROM pg_attribute a, pg_class c, pg_type t, pg_namespace n

WHERE n.nspname = 'public'

AND a.attnum > 0

AND a.attrelid = c.oid

AND a.atttypid = t.oid

AND n.oid = c.relnamespace

ORDER BY c.relname, a.attnum;

LANGUAGE sql;