BackUp БД postgresql через pg_dump не работает при вызове команды из Spring

#java #база_данных #postgresql #spring #kotlin

Дано:

БД postgresql на Mac. В ней есть данные и я могу сделать BackUp из консоли вызвав
/Library/PostgreSQL/10/bin/pg_dump -U db_user_name -w -c -f my_db_name.sql my_db_name,
что создаст файл my_db_name.sql в папке где я вызываю команду.

Задача:

Настроить периодический BackUp БД через Spring.

Проблема:

При вызове вышеуказанного скрипта программно получаю ошибку Файл не найден

Вопрос:

Как же это реализовать? Кажется, проблема в правах доступа к файлам/папкам, но непонятно
какая именно и как узнать конкретно.

Пробовал:

Пробовал указывать абсолютный путь типа {ПУТЬ_К_ПРОЕКТУ_СПРИНГА}/my_db_name.sql или
/Users/myusername/my_db_name.sql - не помогает.

Код:

@Scheduled(cron = "0 * * * * *")
fun backUpDb() {
    val executeCmd = "/Library/PostgreSQL/10/bin/pg_dump -U $dbUserName -w -c -f
$database.sql $database"

    val runtimeProcess: Process
    try {
        val pb = ProcessBuilder(executeCmd)
        pb.redirectOutput(Redirect.INHERIT)
        pb.redirectError(Redirect.INHERIT)
        runtimeProcess = pb.start()
        val processComplete = runtimeProcess.waitFor()

        if (processComplete == 0) {
            println("Backup created successfully")
        } else {
            println("Could not create the backup")
        }
    } catch (ex: Exception) {
        ex.printStackTrace()
    }
}


Лог:

java.io.IOException: Cannot run program "/Library/PostgreSQL/10/bin/pg_dump -U db_user_name
-w -c -f my_db_name.sql my_db_name": error=2, No such file or directory
    at java.lang.ProcessBuilder.start(ProcessBuilder.java:1048)
    at ru.scp.quiz.service.quiz.QuizServiceImpl.backUpDb(QuizServiceImpl.kt:75)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.springframework.scheduling.support.ScheduledMethodRunnable.run(ScheduledMethodRunnable.java:65)
    at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:54)
    at org.springframework.scheduling.concurrent.ReschedulingRunnable.run(ReschedulingRunnable.java:93)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180)
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at java.lang.Thread.run(Thread.java:748)
Caused by: java.io.IOException: error=2, No such file or directory
    at java.lang.UNIXProcess.forkAndExec(Native Method)
    at java.lang.UNIXProcess.(UNIXProcess.java:247)
    at java.lang.ProcessImpl.start(ProcessImpl.java:134)
    at java.lang.ProcessBuilder.start(ProcessBuilder.java:1029)

    

Ответы

Ответ 1

Проблема оказалась в том, что команда в одной строке не распознаётся и надо составлять
массив из каждой её части и тогда оно работает. Т.е. надо команду в массив превратить
при передаче ProcessBuilder-у:

val pb = ProcessBuilder(executeCmd.split(" "))

Сортировка внутри JSON (sql, Postgres)

#sql #json #postgresql

Есть таблица в Postgres, как на картинке. Формат данных в before и after - JSON.
Необходимо сравнить данные из before и after внутри zone_limitation. Основная проблема
заключается в том, что данные могут иметь разную сортировку, а при разной сортировке
такие JSON-ячейки будут восприниматься, как разные, даже если будут иметь одинаковые
цифры внутри.
    

Ответы

Ответ 1

в postgresql есть операторы @> и <@ для jsonb 

эти операторы проверяют структурного вхождение одного jsonb в другой (не взирая на
последовательность ключей), т.о. если вам нужно проверить эквивалентность, то можно
использовать комбинацию этих операторов, например:

with t as (
  select 
    "id",
    "before"#>'{"custom_targeting","include","zone_limitation"}' as "b",
    "after"#>'{"custom_targeting","include","zone_limitation"}' as "a"
  from "test"
)
select 
  t.id "id",
  (t.a @> t.b and t.a <@ t.b)::boolean "Checker"
from t 
order by 1


*данный ответ базируется на dbfiddle от 2SRTVF, см. оригинальный ответ:  https://ru.stackoverflow.com/a/858064/265453


Ответ 2

create table "test" (
  "id" int,
  "before" jsonb,
  "after" jsonb
);






insert into "test" ("id","before","after") values
(1,'{"custom_targeting":{"include":{"zone_limitation":["1","2"]}}}','{"custom_targeting":{"include":{"zone_limitation":["2","1"]}}}'),
(2,'{"custom_targeting":{"include":{"zone_limitation":["1","3"]}}}','{"custom_targeting":{"include":{"zone_limitation":["1","5"]}}}');






with t0 as
(
  select 
    "id",
    json_array_elements(("before"#>'{"custom_targeting","include","zone_limitation"}')::json)::text "lz"
  from "test"
),
t1 as (
  select 
    "id",
    json_array_elements(("after"#>'{"custom_targeting","include","zone_limitation"}')::json)::text "lz"
  from "test"
)
select 
  case when t0."id" is null then t1."id" else t0."id" end "id",
  (min(case when t0."id" is null or t1."id" is null then 0 else 1 end))::boolean
"checher"
from t0 
 full join t1 on t0."id" = t1."id" and t0."lz" = t1."lz"
group by 1
order by 1



id | checher
-: | :------
 1 | t      
 2 | f      



db<>fiddle here


Ответ 3

Не совсем понял, при чём тут сортировка. Вы пишете, что надо сравнить поля в пределах
одной записи.  
Вообще говоря, такая таблица нарушает, насколько помню, даже первую нормальную форму
— элементарность столбцов. Разумеется, никакой SQL-сервер с такими столбцами внятно
работать не умеет.  
Можно, например, вынести из строк информацию, с которой вы намерены работать, в дополнительные
столбцы. Например, коли уж вам нужен zone_limitation, сдублировать (либо вынести) в
отдельные столбцы.  
Можно написать свой тип, написать к нему функций и операций.

Ошибка при вставке новой записи в базу данных через Entity Framework при наличии начальных данных

#c_sharp #postgresql #entity_framework_core

Генерируется база данных с начальными данными. 

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
        modelBuilder.Entity().HasData(
        new Chair[]
        {
            new Chair {Id = 1, Name = "ПМИ" },
            new Chair {Id = 2, Name = "АСОИУ" },
            new Chair {Id = 3, Name = "ИЯ" },
            new Chair {Id = 4, Name = "СИБ" }
        });

        base.OnModelCreating(modelBuilder);

        modelBuilder.ApplyConfiguration(new ChairConfiguration());
    }
}

public class ChairConfiguration : IEntityTypeConfiguration
{
    public void Configure(EntityTypeBuilder builder)
    {
        builder.HasKey(c => c.Id);
        builder.Property(c => c.Name).IsRequired();
    }
}


Потом в программе считывается файл, где может оказаться, что нет необходимой записи
в базе данных, тогда ее туда нужно добавить. 

private int GetChairIndex(string chairName)
{
        using (ApplicationContext db = new ApplicationContext())
        {
            if (db.Chairs.Where(c => c.Name == chairName).ToList().Count() == 0)
            {
                var chair = new Chair(chairName);
                db.Chairs.Add(chair);
                db.SaveChanges();
            }
            return db.Chairs.Where(c => c.Name == chairName).Single().Id;
        }
}


На этом этапе выскакивает исключение из-за повторения ключа (Id). Причем при первом
запуске происходит попытка новой записи присвоить Id = 1, при каждом следующем запуске
Id увеличивается каким-то магическим образом. В итоге, на пятой попытке происходит
успешное добавление. Почему Entity Framework сразу не может понять, какой Id нужно
присвоить новой записи?

Класс Chair:

public class Chair
{
    public int Id { get; set; }
    public string Name { get; set; }

    public List Rooms { get; set; }
    public List Disciplines { get; set; }
    public List Groups { get; set; }

    public Chair()
    {
        Rooms = new List();
        Disciplines = new List();
        Groups = new List();
    }

    public Chair(int id, string name)
    {
        Id = id;
        Name = name;
        Rooms = new List();
        Disciplines = new List();
        Groups = new List();
    }

    public Chair(string name)
    {
        Name = name;
        Rooms = new List();
        Disciplines = new List();
        Groups = new List();
    }

    public override bool Equals(object obj)
    {
        if (obj.GetType() != this.GetType())
        {
            return false;
        }

        Chair c = (Chair)obj;
        return (this.Id == c.Id && this.Name == c.Name);
    }
}

    

Ответы

Ответ 1

Решение проблемы оказалось интересным и спорным, указано оно здесь: https://github.com/npgsql/Npgsql.EntityFrameworkCore.PostgreSQL/issues/367

Предлагается два способа:


Вставлять начальные данные с отрицательными индексами;


    modelBuilder.Entity().HasData(
            new Chair[]
            {
                new Chair {Id = -1, Name = "ПМИ" },
                new Chair {Id = -2, Name = "АСОИУ" },
                new Chair {Id = -3, Name = "ИЯ" },
                new Chair {Id = -4, Name = "СИБ" }
            });



При инициализации базы данных начинать индексацию с определенного значения. В моем
случае с 5, так как 4 записи уже вставлены.


            modelBuilder.HasSequence("ChairsIds")
                .StartsAt(5);
            modelBuilder.Entity()
                        .Property(c => c.Id)
                        .HasDefaultValueSql("nextval('\"ChairsIds\"')");

Как перенести базу из Mysql в PostgreSQL?

#mysql #postgresql

Решил попробовать перенести базу из Mysql в PostgreSQL - сделал sql файл, но из отличия
в синтаксисе-это не работает.
Читал Миграция с MySQL на PostgreSQL , но не разобрался что-то.
Как легко перенести базу ?
Посоветуйте хорошую IDE для PostgreSQL ?
    

Ответы

Ответ 1

Вот хорошая, проверенная веременем библиотека: https://github.com/maxlapshin/mysql2postgres.


Ответ 2

Большой набор утилит можно найти здесь https://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL#MySQL

Проблема с миграцией базы данных на postgres

#ruby_on_rails #postgresql

При создании базы возникает следующая ошибка:

andrey@asus:~/project/odnogrupniki$ rake db:create:all
FATAL:  Peer authentication failed for user "odnogrupniki"


логи целиком: ТУТ

databse.yml:

default: &default
  adapter:  postgresql
  encoding: unicode
  pool: 5
  timeout: 5000
  username: 'odnogrupniki'
  password: '1111'

development:
  <<: *default
  database: odnogrupniki

test:
  <<: *default
  database: odnogrupniki_test

production:
  <<: *default
  database: odnogrupniki_production


в Gemfile добавлял: gem 'pg'
 Bundle - выполнял

andrey@asus:~/project/odnogrupniki$ psql --version
psql (PostgreSQL) 9.5.1


Пользователя создавал так:

postgres@asus:/home/andrey/project/odnogrupniki$ createuser odnogrupniki -P -S -R -D
Enter password for new role: 
Enter it again:

    

Ответы

Ответ 1

Локально можно настроиться на работу по peer athentication, но для этого в базе данных
должен быть пользователь с таким же именем, какой у подключающегося логин в операционной
системе. Для разработки это можно настроить максимально быстро на чистом дистрибутиве.

Чтобы ActiveRecord подключался, удостоверяясь только с помощью учётной записи подключающегося
через Unix domain socket, то надо убрать логин, пароль и хост из настроек подключения.
Совсем. А сделать "себя" суперпользователем в БД на свежеустановленном PostgreSQL (когда
пользователь там всего один, postgres) можно в одну команду:

sudo -u postgres createuser --superuser $(whoami)




\______________/ \______________________________/
  Притворившись    Создать суперпользователя с
   postgres      именем, выведенным командой whoami


А дальше обычное rake db:create и прочее.

Плюсов у такого подхода хватает:


Работает с настройками по умолчанию: установил, сделал себя-администратора и вперёд
Учётные данные ни в какой момент не существуют в рабочем дереве


На "боевом" сервере так делать не стоит, но это уже совсем другая история...


Ответ 2

Замените в конфигурационном файле pg_hba.conf метод аутентификации, вместо аутентификации
по системным учетным записям

local all postgres peer


укажите аутентификацию по паролю

local all postgres md5

Показатели производительности и “здоровья ” СУБД postgres

#postgresql

Подскажите какие показатели производительности и здоровья БД стоит мониторить в postgresql?
    

Ответы

Ответ 1

В первую очередь конечно нужно мониторить основные параметры ОС. Загрузка ЦПУ, Память,
Своп, Диски. В случае с Линуксом помогут утилиты (top, htop, iostat, free)
Далее нужно смотреть какие запросы отнимают наибольшее количество процессорного времени,
и конечно же стараться их оптимизировать. В этом помогут pg_stat_statements, powa (http://dalibo.github.io/powa/) 
Далее очень важно мониторить статистику таблиц такие как index_scan, sequential_scan
и локи (в идеале должно быть меньше seq_scan-ов). Эти данные можно снимать с таблиц
pg_stat_user_tables, pg_locks.
Если используется connection polling то нужно следить за количеством соединений.




Ну и по хорошему нужно прикрутить мониторинг фреймворк для сборки и хранения статистики.
Лично я использую sensu (http_s://sensuapp.org/
) уже с готовыми коммьюнити плагинами (http_s://github.com/sensu-plugins/sensu-plugins-postgres).
Ну и конечно же powa.
В случае проблем можно конечно еще много что посмотреть, но вот это основные параметры
и в основном их хватает.

Переход на PostgreSQL

#php #postgresql #symfony2

В общем есть сайт на symfony меняю драйвер на pdo_pgsql, фэйлится фиксура которая
создает юзера default. 


  INSERT INTO user (Usr_Username, Usr_Email,
  
  ERROR:  syntax error at or near "user"


В общем система хочет чтобы user был в кавычках так как есть такая же переменная 

Так написана фикстура

 $entity = new User();
            $entity->setUsername("root");
            $manager->persist($entity);
 $entity
            ->setPassword("bla")
            ->setEmail("bla@gmail.com")
            ->setFirst("root")
            ->setLast("root")
            ->setOfficePhone("1")
            ->setMobilePhone("1")
            ->setTimeZone("11")
            ->setEnabled(true);
 $manager->flush();


В общем как бы это можно было исправить?
    

Ответы

Ответ 1

В общем оставлю для себя заметку, в User Entity нужно название таблицы написать в
3х кавычках

/**
 * @ORM\Table(name="""user""")
 */


Дальше по аналогии в общем, если есть проблемы, у меня была только в этом месте.


Ответ 2

user - ключевое слово в Postgresql, на мой взгляд, лучше было бы использовать иное
название таблицы или префикс.

/**
 * @ORM\Table(name="users")
 */

Фиксированное значение столбца (= null) в зависимости от значения другого столбца

#база_данных #postgresql

В таблице tablename есть столбцы type и arg. Нужно ввести ограничение, по которому
при type = 1, всегда arg = null.

Каким образом это можно организовать в PostgreSQL? Версия 9.5.2.

Можно реализовать это на уровне приложения, но требуют, именно, на уровне СУБД. Чтобы
она не дала нарушить это правило.
    

Ответы

Ответ 1

ALTER TABLE test 
ADD CONSTRAINT null_arg 
CHECK ((type = 1 AND arg IS NULL) OR type <> 1)

Категории изменчивости функции (PostgreSQL)

#postgresql

Когда какую волатильность выставлять у функции (VOLATILE, STABLE, IMMUTABLE)? 
    

Ответы

Ответ 1

Документация оригинальная, пока актуальный перевод от российских контрибьютеров

Выставлять необходимо минимально необходимую. От этого зависит, что оптимизатор сможет
полезного сделать. Например, выполнить функцию один раз заранее и потом пользоваться
только результатом.


Изменчивая функция (VOLATILE) может делать всё, что угодно, в том    числе, модифицировать
базу данных. Она может возвращать различные    результаты при нескольких вызовах с
одинаковыми аргументами. Оптимизатор не делает никаких предположений о поведении таких
функций. В запросе, использующем изменчивую функцию, она будет вычисляться заново для
каждой строки, когда потребуется её результат.
Стабильная функция (STABLE) не может модифицировать базу данных и    гарантированно
возвращает одинаковый результат, получая одинаковые    аргументы, для всех строк в
одном операторе. Эта характеристика    позволяет оптимизатору заменить множество вызовов
этой функции одним.    В частности, выражение, содержащее такую функцию, можно безопасно
   использовать в условии поиска по индексу. (Так как при поиске по    индексу целевое
значение вычисляется только один раз, а не для каждой    строки, использовать функцию
с характеристикой VOLATILE в условии   поиска по индексу нельзя.)    
Постоянная функция (IMMUTABLE) не может модифицировать базу данных и    гарантированно
всегда возвращает одинаковые результаты для одних и   тех же аргументов. Эта характеристика
позволяет оптимизатору    предварительно вычислить функцию, когда она вызывается в
запросе с   постоянными аргументами. Например, запрос вида SELECT ... WHERE x = 2 +
2 можно упростить до SELECT ... WHERE x = 4, так как нижележащая функция оператора
сложения помечена как IMMUTABLE.
И ещё по таким функциям можно строить функциональные индексы.

Не видит драйвер QPSQL на другой машине Qt

#qt #postgresql

Собрал релиз версию программы, которая работает с postgresql через qt-шный драйвер.
Перенес туда все необходимые dll-ки, перенес папку plugins с драйвером. Экзешник запускается,
драйвер видит, все замечательно. Но, когда переношу на другую машину драйвер qpsql
не распознается. Я не уверен, но может быть на другую машину тоже нужно ставить клиент
postgresql? Подскажите, кто сталкивался с подобной проблемой. 

UPD:

В корне у меня находятся постгресовские dll:
libeay32.dll
libintl.dll
libpq.dll
ssleay.dll

DependencyWalker просит для самого драйвера:

LIBGCC_S_DW2-1.DLL
LIBPQ.DLL
LIBSTDC++-6.DLL
QT5CORE.DLL
QT5SQL.DLL


Но это, вероятно, нормально потому что он находится в \plugins, а то что он просит
есть в корне.

dll постгреса просят следующее:

MSVCR120.DLL
IESHIMS.DLL


Вероятно, msvcr120 - это и есть vcredist. Но, я поставил vcredistx86 и это не помогло.
Компилил mingw32.
    

Ответы

Ответ 1

Нет, клиент PostgreSQL ставить не нужно. Нужны 3 dll-библиотеки из комплекта PostgreSQL:
libintl8.dll, libpq.dll, lib(забыл)-2.dll (это приблизительные названия, могу ошибаться,
сейчас с собой нет Postgres). Располагаться они должны рядом с основным исполняемым
файлом. Кроме того, Postgres скомпилирован в Visual Studio, поэтому вам понадобится
установить vcredist. Необходимую версию можно узнать с помощью DependencyWalker. А
также соблюдайте разрядность. Она должна быть одинаковая у вашей программы, у vcredist,
у библиотек Postgres.

Драйвер базы данных необходимо поместить в папку sqldrivers. Причем, чтобы всё заработало,
мне пришлось самому компилировать этот драйвер. Но раз у вас на основной машине всё
работает, то, скорее всего, это не понадобится.

Если не найдёте, точные названия могу написать завтра.



UPD. Судя по названию MSVCR120.DLL, требуется vcredist версии 2013. ieshims.dll -
это Internet Explorer. Скорее всего, этот файл будет не нужен. Файлы вроде Qt5Core.dll,
Qt5Sql и др. должны быть рядом с исполняемым файлом, а файл qsqlpsql.dll должен быть
в папке sqldrivers.



UPD. Вот список нужных DLL:

libiconv-2.dll
libintl-8.dll
libpq.dll


Ответ 2

Попробуйте так:

int main(int argc, char * argv[])
{
    qApp->addLibraryPath("plugins"); // путь к папке с плагинами 
                                     // относительно рабочей папки процесса

    QApplication application(argc, argv);
    // ... все остальное
    return 0;
}

Что означает CONSTRAINT в данном контексте?

#sql #postgresql #constraints

Есть 2 таблицы :

CREATE TABLE company(
    id integer NOT NULL,
    name character varying,
    CONSTRAINT company_pkey PRIMARY KEY (id)
);

CREATE TABLE person(
    id integer NOT NULL,
    name character varying,
    company_id integer,
    CONSTRAINT person_pkey PRIMARY KEY (id)
);


Скрипт писал не я, и пытаюсь понять логику человека который это делал. В чем может
быть смысл этого ограничения CONSTRAINT person_pkey PRIMARY KEY (id)? И во втором случае
аналогично. Это ограничение что id может быть только первичным ключем ? А зачем это
может быть нужно? Тем более еще и именовать это ограничение. Помогите пожалуйста разобраться
зачем делать такой CONSTRAINT?
    

Ответы

Ответ 1

Определение

CONSTRAINT company_pkey PRIMARY KEY (id)


эквивалентно определению

PRIMARY KEY (id)


и означает, что id является первичным ключом таблицы. 

Т.к. в данном случае первичный ключ состоит из одного столбца, то его можно было
бы указать на уровне поля:

CREATE TABLE company(
    id integer PRIMARY KEY,
    name character varying
);


Возможность определения ключа на уровне таблицы полезна если ключ — составной

PRIMARY KEY (id, name)


В первом случае у ограничения задано имя. Это имя будет выводиться в сообщениях об
ошибках. Также по имени можно это ограничение удалить. В случае если имя ограничения
не задано явно, оно будет сгенерировано СУБД.

Это сказано в документации:


  CONSTRAINT constraint_name
  An optional name for a column or table constraint. If the constraint is violated,
the constraint name is present in error messages, so constraint names like col must
be positive can be used to communicate helpful constraint information to client applications.
(Double-quotes are needed to specify constraint names that contain spaces.) If a constraint
name is not specified, the system generates a name.

Как в ASP.Net Core работать с PostgreSQL?

#c_sharp #postgresql #aspnet_core

С помощью каких библиотек Вы работаете в ASP.Net Core с PostgreSQL?
    

Ответы

Ответ 1

Можете использовать


Entity Framework.
Чистый ADO.NET.
Dapper (методы расширения для ADO.NET).
Рабочий пример.

Как сгенерировать тысячу / миллион / миллиард записей в таблице?

#sql #postgresql

Для оценки производительности моего приложения нужно наполнить БД большим количеством
тестовых данных. Встала задача заполнить имеющуюся таблицу автоматически сгенерированными
данными. Думал сделать это с помощью одного или нескольких SQL-запросов. Однако столкнулся
с затруднением с циклами — кажется, они не дают возможности генерирования произвольных
последовательностей. Может быть, есть вариант построить временную таблицу с бесконечным
количеством строк, вызывав какую-нибудь встроенную функцию?

К примеру, можно вопрос конкретизировать: как автоматически заполнить таблицу нижеследующими
данными?

|col1|col2|col3|col4|
|----|----|----|----|
|   0|   1|   0|    |
|   1|   2|   1|    |
|   2|   3|   0|    |
|   4|   4|   1|    |
|   8|   5|   0|    |
|  16|   6|   1|    |
    .   .   .   .
| 2^N| N+1| N%2|  * |


* любая формула по-вашему усмотрению
    

Ответы

Ответ 1

Пока составлял вопрос — нашёл решение :)

INSERT INTO t(col1, col2, col3)
SELECT 2 ^ k, k + 1, k % 2
FROM generate_series(0, N) AS k


https://postgrespro.ru/docs/postgrespro/10/functions-srf

PostgreSQL. Можно ли очистить очень большую таблицу, если на диске нет места?

#postgresql #администрирование

Собственно, вопрос в заголовке.
PostgreSQL 10. Есть большая таблица на несколько десятков гигабайт и несколько сотен
миллионов записей. А вот места на диске совсем нет (пару сотен мегабайт).

Есть ли какие-нибудь колдовские заклинания, чтобы из этой таблички почистить все
или часть записей, не имея свободного места?
    

Ответы

Ответ 1

Да, колдовские заклинания есть.

С полной очисткой таблицы справится truncate, требующий на диске только несколько
десятков килобайт (под новую пустую таблицу, индексы, да занести информацию в WAL),
но только полная очистка.

Если необходимо удалить не всё, но места нет - то необходимо делать:


delete всего более ненужного
vacuum tablename
пустые update нужных строк частями, ничего на самом деле не изменяющие

 update tablename set column=column where ...

такие update пометят строки удалёнными где те были и создадут копию в начале таблицы
последующий vacuum tablename сможет возвращать место операционной системе если в
конце таблицы остались только пустые страницы без живых данных


Основной фокус - придумать как перемещать только строки из конца таблицы. Индексы
же только перестраивать. Можно через удаление и построение обратно, раз всё равно авария
и места для работы нет.

Проблема у этого метода если у вас распухла не сама табличка, а её TOAST часть. Тогда
таким способом не лечится.



Существует специально обученный perl скрипт pgcompacttable специально написанной
для сжатия таблиц в условиях недостатка дискового места и автоматизирующий описанные
манипуляции манипуляции.

Обрезать получаемый реультат в PostrgeSQL

#sql #postgresql

Нужно вытащить данные с БД так чтобы они были уникальными и обрезать их.
Обрезать нужно все после останнего символа в строке - '_' (обрезать включая символ '_').
То есть если у нас в поле name имеются значения - 'Tom_1', 'Tom_2', 'Tom_Rob_3' должно
вывести - "Tom", "Tom_Rob"

SELECT DISTINCT ON (name)
FROM user


Как это сделать?
    

Ответы

Ответ 1

SELECT DISTINCT ON(name_trimmed)
LEFT(name, length(name) - position('_' in reverse(name))) AS name_trimmed
FROM "user"


Пример в SQL Fiddle

Как сделать три уровня кавычек в bash?

#linux #postgresql #bash

Добрый день, коллеги!
Как отредактировать следующую строку для её корректного исполнения?

$ su postgres -c 'psql -c "alter role postgres with password 'postgres';"'


Проблема, собственно, в средних кавычках (password 'postgres')
Скрипт выполняется от имени root. 

p.s. есть обходной вариант - пометить строку psql -c "alter role postgres with password
'postgres';" в ещё один sh-скрипт, и уже его выполнять через su user -c, но я не считаю
это правильным.
    

Ответы

Ответ 1

Попробуйте так: 

# su postgres -c 'psql -c "alter role postgres with password '"'"'postgres'"'"';"'


Ответ 2

Экранирование же ж в помощь:

su postgres -c "psql -c \"alter role postgres with password 'postgres';\""


Ответ 3

Один из -c можно исключить следующим образом:

$ sudo -u postgres -- psql -c "alter role postgres with password 'postgres';"


Ответ 4

В одинарных кавычках все символы. Используйте двойные кавычки в них можно экранировать
одинарные и двойные.

su postgres -c "psql -c \"alter role postgres with password \'postgres\' ;\""


Ответ 5

для больших простыней я бы рекомендовал Встроенные документы << EOF

Как изменить тип колонки на serial?

#postgresql

Есть таблица с заполненными данными: 

CREATE TABLE "Виплата"
(
  old integer NOT NULL,
  "Код_договору" integer,
  "Дата" timestamp(0) without time zone,
  "Сума_виплат" text,
  "Оплата" boolean,
  "Код" integer,
  CONSTRAINT "Виплата_pkey" PRIMARY KEY (old)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE "Виплата"
  OWNER TO postgres; 


Обязательно нужно изменить тип колонки "Код" на serial! 
Обычным ALTER: ALTER TABLE "Виплата" ALTER COLUMN "Код" type serial;  не получается
это сделать, потому что serial не тип  ERROR:  type "serial" does not exist  ! Но сделать
это очень нужно. 

Каким способом можно это реализовать ?
    

Ответы

Ответ 1

Тип SERIAL аналогичен полю, значение которого устанавливается из последовательности.
    Поэтому делаем так:

CREATE SEQUENCE code_seq;
ALTER TABLE "Виплата" ALTER COLUMN "Код" SET DEFAULT nextval('code_seq');


Не знаю как на мове правильно будет "Последовательность_коду", поэтому просто code_seq.
В nextval() передаётся строка с название последовательности, поэтому там одинарные
кавычки должны быть.

sql запрос на удаление

#sql #postgresql

База данных postgreSQL , есть две таблицы , "groups" (group_id (pk), name) - 6 строк и 
"students" (student_id(pk), group_id, first_name, last_name) - 43 строки.  

Как удалить строки из таблицы "students" по конкретному названию (groups.name) из
таблицы "groups"
На вот такой запрос 

DELETE
FROM 
  public."GROUPS", 
  public."STUDENTS"
WHERE 
  "GROUPS"."GROUP_ID" = "STUDENTS"."GROUP_ID" AND "GROUPS"."NAME" = "SR-01";


пишет 


  ошибка синтаксиса (примерное положение: ",")


на вот такой запрос

DELETE
FROM 
  public."STUDENTS"
WHERE 
  "GROUPS"."GROUP_ID" = "STUDENTS"."GROUP_ID" AND "GROUPS"."NAME" = "SR-01";


пишет 


  таблица "GROUPS" отсутствует в предложении FROM

    

Ответы

Ответ 1

У PostgreSQL довольно забавный синтаксис удаления с использованием ключевого слова USING:

DELETE FROM public."STUDENTS"
      USING public."GROUPS"
      WHERE "GROUPS"."GROUP_ID" = "STUDENTS"."GROUP_ID"
        AND "GROUPS"."NAME" = 'SR-01'


В крайнем случае всегда можно использовать старые, добрые подзапросы:

DELETE FROM public."STUDENTS"
 WHERE "STUDENTS"."GROUP_ID" IN(SELECT "GROUP_ID"
                                  FROM public."GROUPS"
                                 WHERE "NAME" = 'SR-01')


Ответ 2

DELETE
FROM 
  public."STUDENTS"
  using public."groups"
WHERE 
  "GROUPS"."GROUP_ID" = "STUDENTS"."GROUP_ID" AND "GROUPS"."NAME" = "SR-01";


фиддл

Как получить время из PostgreSQL в том же самом часовом поясе, в котором и было изначально загружено в БД, вместо UTC?

#python #django #postgresql #timezone

Есть данные, которые через Django загружаются в базу в формате “2015-10-31 17:00:00+03”
(aware форма времени).
PostgreSQL имеет свойство сохранять время в UTC.
В pgAdmin-е я вижу время не в UTC, а в том виде, в котором было изначально загружено
значение, т.е. “2015-10-31 17:00:00+03”.


К примеру я загружаю новые данные, но перед заливкой в БД нужно сравнить эти данные
со значениями, которые уже есть в БД.
Новая порция идет в формате: 

"2015-10-31 17:00:00+03“,
а значения из БД вытягиваются уже в UTC, т.е. 

”2015-10-31 14:00:00+00".

Как сделать так, чтоб значение вытягивалось из БД не в UTC, а в том же часовом поясе,
в котором и было залито?

test = Shows.objects.get(name='Test')
test.date_time # выдает в UTC, а нужно чтоб было +3


П.С Хардкодить нельзя. Нужно получать именно тот часовой пояс из БД, что и был залит
изначально со значением, так как заливаться значения могут с разным часовым поясом.
    

Ответы

Ответ 1

Документация django говорит, что PostgreSQL преобразует местное время (время c часовой
зоной, указанной для текущего соединения с базой данной) в UTC при сохранении и наоборот
преобразует из UTC в местное время при чтении.


  Как сделать так, чтоб значение вытягивалось из БД не в UTC, а в том же часовом
поясе, в котором и было залито?


Если USE_TZ=True, то для соединения используется UTC временна́я зона, то есть postgresql
видит только UTC.

Если Вы сохраняете время, принадлежащее нескольким временны́м зонам, то дополнительно
к самому времени с типом timestamptz, сохраняйте также и имя временно́й зоны (например,
'Europe/Kiev' или 'Europe/Moscow').

Если важно получить тоже самое локальное время, даже если правила изменились (если
другая версия tzdata используется), то сохраняйте также явно текущий utc offset в минутах
и другую необходимую информацию отдельно.

Зная, UTC время и имя зоны, можно получить желаемое время (используя текущие правила
(tzdata version)) в Питон коде:

import pytz # $ pip install pytz

local = utc.astimezone(pytz.timezone('Europe/Kiev'))


Чтобы запросить время из базы данных напрямую в заданной временно́й зоне:

select your_date at time zone 'Europe/Kiev' from your_table


Зная, UTC время и utc offset в минутах:

local = utc.astimezone(pytz.FixedOffset(120))


Чтобы получить время в текущей временно́й зоне для django (зона, которая используется
для показа в шаблонах):

from django.utils import timezone

local = timezone.localtime(utc)


Ответ 2

Воспользуйтесь pytz

>>> from datetime import datetime, timedelta
>>> from pytz import timezone
>>> import pytz
>>> utc = pytz.utc
>>> utc.zone
'UTC'
>>> eastern = timezone('US/Eastern')
>>> eastern.zone
'US/Eastern'
>>> amsterdam = timezone('Europe/Amsterdam')
>>> fmt = '%Y-%m-%d %H:%M:%S %Z%z'

>>> loc_dt = eastern.localize(datetime(2002, 10, 27, 6, 0, 0))
>>> print loc_dt.strftime(fmt)
2002-10-27 06:00:00 EST-0500

>>> ams_dt = loc_dt.astimezone(amsterdam)
>>> ams_dt.strftime(fmt)
'2002-10-27 12:00:00 CET+0100'

PostgreSQL: максимальное и текущее количество соединений

#postgresql

Как можно узнать максимальное и текущее количество установленных соединений на сервере
PostgreSQL?
    

Ответы

Ответ 1

За максимальное количество соединений отвечает параметр max_connections, получить
который можно при помощи запроса

SHOW max_connections;


Количество подключенных к серверу соединений можно получить, выполнив запрос

SELECT COUNT(*) FROM pg_stat_activity;