Nyxynyx's questions

Criei um backup do meu banco de dados em um PostgreSQL 11.6 (com extensão TimescaleDB 1.60) usando pg_dump:

PGPASSWORD=mypassword pg_dump -h 127.22.0.4 -p 5432 -U postgres -Z0 -Fc database_development

e restaurou-o em um novo servidor executando as mesmas versões do PostgreSQL 11.6 (com extensão TimescaleDB 1.60) usando pg_restore. Para a restauração, executei os seguintes comandos psqlcomo usuário postgres:

CREATE DATABASE database_development;
\c database_development
CREATE EXTENSION timescaledb;
SELECT timescaledb_pre_restore();

\! time pg_restore -Fc -d database_development /var/lib/postgresql/backups/database_development_2020-02-29

SELECT timescaledb_post_restore();

O tamanho do banco de dados original era de 389 GB, mas o banco de dados restaurado era de 229 GB. Esses tamanhos foram obtidos executando

select pg_size_pretty(pg_database_size('database_development'))

Algumas diferenças:

O banco de dados antigo é armazenado em uma partição ext4, enquanto o novo banco de dados é armazenado em um sistema de arquivos ZFS com compactação desabilitada. Ambas as instâncias de banco de dados estão sendo executadas em um contêiner do Docker com um host Ubuntu 18.04.

Pergunta: Como podemos explicar as diferenças nos tamanhos dos bancos de dados? Não foram encontrados erros durante o pg_dumpe pg_restore.

Eu preciso usar o programa pg_basebackup/ pg_dumpem um sistema Ubuntu 18.04 para conectar a um servidor PostgreSQL 11.6 remoto.

No entanto, o sistema atual contém apenas pg_basebackup10.10 e o banco de dados remoto está usando o PostgreSQL 11.6. Correndo pg_basebackupdá o erro

pg_basebackup: servidor incompatível versão 11.6

Tentei instalar a versão correta pg_basebackupusando o comando:

apt install postgresql-client-11

mas o pacote apt não foi encontrado. Apenas postgresql-client, postgresql-client-10e postgresql-client-commonestão disponíveis os repositórios apt do sistema.

Descrição : Como podemos instalar a versão correta do pg_basebackupsem ter que copiá-la do servidor de banco de dados remoto e sem instalar o PostgreSQL 11.6 nesta máquina?

Um servidor Ubuntu 16.04 tem seu fuso horário definido para America/New_Yorkusar o comando

sudo timedatectl set-timezone America/New_York

Isso é confirmado executando os comandos

status timedatectl | grep "Fuso horário"

 Time zone: America/New_York (EST, -0500)

data +%Z

EST

No entanto, o MySQL pensa que o fuso horário do sistema ainda é UTC. O servidor MySQL 5.7 (executando dentro de um contêiner docker) foi reiniciado antes de consultar o fuso horário do MySQL

Saída da consulta MySQLshow global variables like '%time_zone'

system_time_zone UTC
time_zone SYSTEM

Algo deu errado?

Depois de definir as várias variáveis ​​para habilitar a compactação nas tabelas do Innodb Barracuda, qual consulta você usará para verificar se essas tabelas estão realmente compactadas?

Estou tentando copiar uma tabela planet_osm_polygonde um banco de dados osmpara outro test. Eu su postgrese executei o pg_dump.

Problema: No entanto, estou recebendo o erro could not change directory to "/root"e o Password:prompt apareceu duas vezes! Existe uma maneira de executar pg_dumpquando logado como root?

root@lalaland:~# su postgres
postgres@lalaland:/root$ pg_dump -h localhost "osm" --table "public.planet_osm_polygon" | 
    psql -h localhost "test" --table "staging.planet_osm_polygon"
could not change directory to "/root"
could not change directory to "/root"
Password: Password:

ATUALIZAR

Problema nº 2: Parece que a tabela foi copiada para o publicesquema, embora eu tenha passado o sinalizador --table="staging.planet_osm_polygon". Por que não é copiado para o esquema staging?

Ao recuperar o banco de dados Postgresql de um servidor Ubuntu 12.04 invadido, copiei o diretório de dados para outro local, reinstalei o sistema operacional e o PostgreSQL, interrompi o serviço PostgreSQL, removi o conteúdo do diretório de dados e copiei de volta o conteúdo da instalação anterior.

Problema: PostgreSQL não inicia agora, apresentando os erros abaixo. Por que isso está acontecendo e como podemos resolver o problema?

service postgresql restart
* Restarting PostgreSQL 9.1 database server                                     

* Error: could not exec /usr/lib/postgresql/9.1/bin/pg_ctl /usr/lib/postgresql/9.1/bin/pg_ctl start -D /var/lib/postgresql/9.1/main -l /var/log/postgresql/postgresql-9.1-main.log -s -o -c config_file="/etc/postgresql/9.1/main/postgresql.conf" :

Atualizar

Reiniciar o serviço PostgreSQL agora dá o erro:

* Restarting PostgreSQL 9.1 database server                                     
* The PostgreSQL server failed to start. Please check the log output:
2013-04-16 01:52:10 EDT PANIC:  could not open control file "global/pg_control": Permission denied

Meu servidor foi hackeado e só tenho acesso FTP para recuperar meus dados antes de reinstalar o sistema operacional.

Quais/onde estão os diretórios que precisam de backup? O MySQL será recuperado de volta ao seu estado inicial se eu copiar esses diretórios de volta para o servidor recém-instalado?

Por que o pgAdmin3 define automaticamente o proprietário da tabela postgresquando é criado e definido OIDS=FALSE? Sou novo no PostgreSQL e quero saber o raciocínio por trás desse comportamento padrão.

-- Table: staging.mytable

-- DROP TABLE staging.mytable;

...

WITH (
  OIDS=FALSE
);

ALTER TABLE staging.mytable
OWNER TO postgres;

Eu tenho uma tabela PostgreSQL com dados existentes e preciso importar todas as linhas de um arquivo CSV para essa tabela. Estou usando a ferramenta de importação do pgadmin3 para fazer a importação.

Pergunta: Para as linhas recém-importadas, como defino os valores para uma coluna específica (não encontrada no CSV)? Não posso fazer UPDATE staging.tablename SET colname = 'somename'o que fazia ao importar para uma tabela vazia. Ou esta abordagem está errada?

Na consulta a seguir, por que temos que limitar os resultados retornados de cada partição usando a cláusula WHERE foo.row_num < 3fora da subconsulta foo, mas não dentro da subconsulta com WHERE row_num < 3?

Consulta

SELECT pid, land_type, row_num, road_name, round(CAST(dist_km AS numeric), 2) AS dist_km

FROM (
    SELECT ROW_NUMBER() 
    OVER (
        PARTITION by loc.pid
        ORDER BY ST_Distance(r.the_geom, loc.the_geom)
    ) as row_num,
    loc.pid, loc.land_type, r.road_name, 
    ST_Distance(r.the_geom, loc.the_geom)/1000 as dist_km
    FROM ch05.land AS loc
    LEFT JOIN ch05.road AS r
    ON ST_DWithin(r.the_geom, loc.the_geom, 1000)
    WHERE loc.land_type = 'police station'
) AS foo

WHERE foo.row_num < 3
ORDER BY pid, row_num;

Consulta que não funciona

SELECT pid, land_type, row_num, road_name, round(CAST(dist_km AS numeric), 2) AS dist_km

FROM (
    SELECT ROW_NUMBER() 
    OVER (
        PARTITION by loc.pid
        ORDER BY ST_Distance(r.the_geom, loc.the_geom)
    ) as row_num,
    loc.pid, loc.land_type, r.road_name, 
    ST_Distance(r.the_geom, loc.the_geom)/1000 as dist_km
    FROM ch05.land AS loc
    LEFT JOIN ch05.road AS r
    ON ST_DWithin(r.the_geom, loc.the_geom, 1000)
    WHERE loc.land_type = 'police station'
    AND row_num < 3
) AS foo

ORDER BY pid, row_num;

Erro: ERROR: column "row_num" does not exist