Início / user-138324

tuk's questions

Martin Hope
tuk
Asked: 2022-07-29 21:52:40 +0800 CST
  • 1

Digamos que eu tenha uma tabela como abaixo

CREATE TABLE empl(
   id SERIAL PRIMARY KEY,
   name VARCHAR NOT NULL,
   address VARCHAR NOT NULL,
   status int NOT NULL,
   metadata varchar NOT NULL
);

e tem dados como

(ID, NAME, ADDRESS, STATUS, METADATA)
(2,  'tuk', 'ind',    1,      'meta')
...
(200,'tuka','eng',    2,      'meta2')
  1. update empl set status = 2 where (id = 2);
  2. update empl set status = 2, name='tuk', address='ind', metadata='meta' where (id = 2);

Alguém pode me dizer se há alguma vantagem de fazer o número 1 sobre o número 2? Qual é geralmente preferido?

Versão do Postgres - 10.17

postgresql
Martin Hope
tuk
Asked: 2022-06-13 10:20:20 +0800 CST
  • 2

Os documentos do Postgres informam o seguinte sobre varreduras somente de índice e índices de cobertura :

se você costuma executar consultas como

SELECT y FROM tab WHERE x = 'key';

a abordagem tradicional para acelerar essas consultas seria criar um índice somente em x. No entanto, um índice definido como

CREATE INDEX tab_x_y ON tab(x) INCLUDE (y);

poderia tratar essas consultas como varreduras somente de índice, porque y pode ser obtido do índice sem visitar o heap.

Como a coluna y não faz parte da chave de pesquisa do índice, ela não precisa ser de um tipo de dados que o índice possa manipular; ele é meramente armazenado no índice e não é interpretado pelo mecanismo do índice. Além disso, se o índice for um índice exclusivo, isso é

CREATE UNIQUE INDEX tab_x_y ON tab(x) INCLUDE (y);

a condição de exclusividade se aplica apenas à coluna x, não à combinação de x e y. (Uma cláusula INCLUDE também pode ser escrita em restrições UNIQUE e PRIMARY KEY, fornecendo sintaxe alternativa para configurar um índice como este.)

Pergunta 1: Se o tipo de dados ypode ser adicionado no índice e não há requisito de exclusividade, há alguma vantagem em usar CREATE INDEX tab_x_y ON tab(x) INCLUDE (y)para CREATE INDEX tab_x_y ON tab(x, y)consultas como SELECT y FROM tab WHERE x = 'key';?

É aconselhável ser conservador ao adicionar colunas de carga útil não chave a um índice, especialmente colunas largas. Se uma tupla de índice exceder o tamanho máximo permitido para o tipo de índice, a inserção de dados falhará. De qualquer forma, as colunas não chave duplicam os dados da tabela do índice e aumentam o tamanho do índice, potencialmente tornando as pesquisas mais lentas.

Pergunta 2: Alguém pode explicar com um exemplo o que wide columnssignifica?

Pergunta 3: Alguém pode explicar a afirmação abaixo no contexto de INCLUDE(y). Se INCLUDEsuportar apenas varreduras de índice y, também terá que ser armazenado em index. Então, como a declaração abaixo não vale para INCLUDE(y).

De qualquer forma, as colunas não chave duplicam os dados da tabela do índice e aumentam o tamanho do índice

postgresql index
Martin Hope
tuk
Asked: 2018-06-12 06:45:25 +0800 CST
  • 0

Uma consulta em caso de replicação assíncrona com slots de replicação e nenhum arquivamento wal se um nó escravo permanecer desconectado do mestre por mais de wal_keep_segments, ele não poderá fazer a replicação quando estiver online.

Agora minha pergunta é quando o escravo se conectar ao mestre novamente, o XLOG no mestre também continuará construindo, pois a replicação dos escravos está travada ou o postgres removerá o slot de replicação se não estiver ocupado / ativo?

Versão do Postgres - 10.4

postgresql
Martin Hope
tuk
Asked: 2018-06-12 02:30:18 +0800 CST
  • 4

Digamos que estou usando replicação de streaming assíncrona com a configuração abaixo em um cluster de 3 nós com Postgres 10.4 e Patroni 1.4.4

    bootstrap:
      dcs:
        ttl: 30
        loop_wait: 10
        retry_timeout: 10
        maximum_lag_on_failover: 1048576
        postgresql:
          use_pg_rewind: true
          use_slots: true
          parameters:
            max_wal_senders: 10
            wal_keep_segments: 100
            max_replication_slots: 10

Vamos supor que um dos nós de réplica de repente perca sua conexão com o primário por um longo tempo.

  1. Nesse caso, acho que o tamanho do WAL no primário continuará crescendo, pois não está sendo consumido pelo slot de replicação da réplica desconectada. Portanto, existe alguma configuração na configuração do patroni que removerá a réplica e removerá seu slot de replicação se for desconectado do primário por x tempo de duração?
  2. Qual é a maneira recomendada de lidar com este caso?
postgresql replication
Martin Hope
tuk
Asked: 2018-03-23 04:45:30 +0800 CST
  • 39

Alguém pode resolver minhas dúvidas abaixo sobre o pg_stat_activitypostgres 9.5?

  1. Alguns podem me informar o que significa o state IDLEdenota na linha de pg_stat_activity? É como verificar todas as conexões abertas para o postgres e se a conexão não estiver executando nenhuma consulta, IDLEdenota a última consulta executada pela conexão?
  2. Na saída de select * from pg_stat_activityestou vendo linhas com state IDLEe com query_start_datemais de 2 dias? O que isso significa? Eu deveria me preocupar com isso?
  3. Alguém pode me informar como pg_stat_activity é preenchido? Se estou executando uma consulta Select * from pg_stat_activity, ela produz os detalhes de algum instantâneo mantido pelo postgres ou verifica as conexões atuais abertas no postgres para fornecer os detalhes?
postgresql
Martin Hope
tuk
Asked: 2017-12-17 19:54:50 +0800 CST
  • 3

Ao tentar instalar o postgres no Ubuntu 14, estou vendo que o postgres está sendo desligado. Estou vendo os erros abaixo nos logs

[19043] LOG:  listening on IPv4 address "127.0.0.1", port 5432
[19043] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
[19044] LOG:  database system was shut down at 2017-12-16 02:41:39 PST
[19043] LOG:  database system is ready to accept connections
[19051] [unknown]@[unknown] LOG:  incomplete startup packet
[19043] LOG:  received fast shutdown request
[19043] LOG:  aborting any active transactions
[19043] LOG:  worker process: logical replication launcher (PID 19050) exited with exit code 1
[19045] LOG:  shutting down
[19043] LOG:  database system is shut down

Alguém pode me informar o que pode estar dando errado? Por que o postgres está sendo encerrado após a instalação?

postgresql errors
Martin Hope
tuk
Asked: 2017-11-25 19:28:15 +0800 CST
  • 0

Estou tentando testar a migração do postgres para 10 de 9.5

Ao tentar um banco de dados de teste de tamanho 490 GB em um nó postgres, está demorando cerca de 18 minutos. O comando que usei

sudo -H -u postgres /usr/lib/postgresql/10/bin/pg_upgrade \
   -b /usr/lib/postgresql/9.5/bin \
   -B /usr/lib/postgresql/10/bin \
   -d /var/lib/postgresql/data/postgresql0 \
   -D /var/lib/postgresql/10/data/postgresql0 \
   -o ' -c config_file=/etc/postgresql/9.5/main/postgresql.conf' \
   -O ' -c config_file=/etc/postgresql/10/main/postgresql.conf' -k

Existe alguma maneira mais rápida de fazer isso?

postgresql upgrade
Martin Hope
tuk
Asked: 2017-11-15 09:38:53 +0800 CST
  • 2

Estou tentando atualizar o postgres 9.5.4 para 10.1 no Ubuntu 14. O diretório de dados para 9.5.4 é/var/lib/postgresql/data/postgresql0

Os passos que eu segui

Instale o postgres 10.1

ubuntu@van-platform:~/build-target/launcher$sudo add-apt-repository "deb http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg main"
ubuntu@van-platform:~/build-target/launcher$wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
ubuntu@van-platform:~/build-target/launcher$ OK
ubuntu@van-platform:~/build-target/launcher$sudo apt-get update
...
ubuntu@van-platform:~/build-target/launcher$sudo apt-get install postgresql-10
Reading package lists... Done
Building dependency tree       
Reading state information... Done
The following packages were automatically installed and are no longer required:
  comerr-dev krb5-multidev libgssrpc4 libkadm5clnt-mit9 libkadm5srv-mit9
  libkdb5-7
Use 'apt-get autoremove' to remove them.
The following extra packages will be installed:
  libpq-dev libpq5 postgresql-client-10 postgresql-client-common
  postgresql-common
Suggested packages:
  postgresql-doc-10 locales-all
The following NEW packages will be installed:
  postgresql-10 postgresql-client-10
The following packages will be upgraded:
  libpq-dev libpq5 postgresql-client-common postgresql-common
4 upgraded, 2 newly installed, 0 to remove and 14 not upgraded.
Need to get 6,595 kB of archives.
After this operation, 26.7 MB of additional disk space will be used.
Do you want to continue? [Y/n] Y
Get:1 http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg/main libpq-dev amd64 10.1-1.pgdg14.04+1 [241 kB]
Get:2 http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg/main libpq5 amd64 10.1-1.pgdg14.04+1 [157 kB]
Get:3 http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg/main postgresql-common all 188.pgdg14.04+1 [220 kB]
Get:4 http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg/main postgresql-client-common all 188.pgdg14.04+1 [81.5 kB]
Get:5 http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg/main postgresql-client-10 amd64 10.1-1.pgdg14.04+1 [1,282 kB]
Get:6 http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg/main postgresql-10 amd64 10.1-1.pgdg14.04+1 [4,613 kB]
Fetched 6,595 kB in 4s (1,473 kB/s)        
Preconfiguring packages ...
(Reading database ... 102799 files and directories currently installed.)
Preparing to unpack .../libpq-dev_10.1-1.pgdg14.04+1_amd64.deb ...
Unpacking libpq-dev (10.1-1.pgdg14.04+1) over (9.5.4-1.pgdg14.04+1) ...
Preparing to unpack .../libpq5_10.1-1.pgdg14.04+1_amd64.deb ...
Unpacking libpq5:amd64 (10.1-1.pgdg14.04+1) over (9.5.4-1.pgdg14.04+1) ...
Preparing to unpack .../postgresql-common_188.pgdg14.04+1_all.deb ...
Leaving 'diversion of /usr/bin/pg_config to /usr/bin/pg_config.libpq-dev by postgresql-common'
Unpacking postgresql-common (188.pgdg14.04+1) over (175.pgdg14.04+1) ...
Preparing to unpack .../postgresql-client-common_188.pgdg14.04+1_all.deb ...
Unpacking postgresql-client-common (188.pgdg14.04+1) over (175.pgdg14.04+1) ...
Selecting previously unselected package postgresql-client-10.
Preparing to unpack .../postgresql-client-10_10.1-1.pgdg14.04+1_amd64.deb ...
Unpacking postgresql-client-10 (10.1-1.pgdg14.04+1) ...
Selecting previously unselected package postgresql-10.
Preparing to unpack .../postgresql-10_10.1-1.pgdg14.04+1_amd64.deb ...
Unpacking postgresql-10 (10.1-1.pgdg14.04+1) ...
Processing triggers for man-db (2.6.7.1-1ubuntu1) ...
Processing triggers for ureadahead (0.100.0-16) ...
Setting up libpq5:amd64 (10.1-1.pgdg14.04+1) ...
Setting up libpq-dev (10.1-1.pgdg14.04+1) ...
Setting up postgresql-client-common (188.pgdg14.04+1) ...
Installing new version of config file /etc/postgresql-common/user_clusters ...
Setting up postgresql-common (188.pgdg14.04+1) ...

Configuration file '/etc/logrotate.d/postgresql-common'
 ==> File on system created by you or by a script.
 ==> File also in package provided by package maintainer.
   What would you like to do about it ?  Your options are:
    Y or I  : install the package maintainer's version
    N or O  : keep your currently-installed version
      D     : show the differences between the versions
      Z     : start a shell to examine the situation
 The default action is to keep your current version.
*** postgresql-common (Y/I/N/O/D/Z) [default=N] ? Y
Installing new version of config file /etc/logrotate.d/postgresql-common ...
Replacing config file /etc/postgresql-common/createcluster.conf with new version
 * Starting PostgreSQL 9.5 database server                               [ OK ] 
Setting up postgresql-client-10 (10.1-1.pgdg14.04+1) ...
Setting up postgresql-10 (10.1-1.pgdg14.04+1) ...
Creating new PostgreSQL cluster 10/main ...
/usr/lib/postgresql/10/bin/initdb -D /var/lib/postgresql/10/main --auth-local peer --auth-host md5
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgresql/10/main ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

Success. You can now start the database server using:

    /usr/lib/postgresql/10/bin/pg_ctl -D /var/lib/postgresql/10/main -l logfile start

Ver Cluster Port Status Owner    Data directory              Log file
10  main    5433 down   postgres /var/lib/postgresql/10/main /var/log/postgresql/postgresql-10-main.log
 * Starting PostgreSQL 10 database server                                [ OK ] 
Processing triggers for libc-bin (2.19-0ubuntu6.13) ...

Crie o novo diretório de dados

support@vrni-platform:~$ sudo -H -u postgres mkdir -p /var/lib/postgresql/10/data/postgresql0

Parar postgres

ubuntu@vrni-platform:~/build-target/launcher$ sudo service postgresql stop
     * Stopping PostgreSQL 10 database server                                [ OK ] 
     * Stopping PostgreSQL 9.5 database server                               [ OK ]

Ativar soma de verificação

support@vrni-platform:/tmp$ sudo -H -u postgres /usr/lib/postgresql/10/bin/pg_ctl -D /var/lib/postgresql/10/data/postgresql0 initdb -o '--data-checksums'
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are enabled.

fixing permissions on existing directory /var/lib/postgresql/10/data/postgresql0 ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    /usr/lib/postgresql/10/bin/pg_ctl -D /var/lib/postgresql/10/data/postgresql0 -l logfile start

Ao tentar verificar a viabilidade da atualização com o comando abaixo

sudo -H -u postgres /usr/lib/postgresql/10/bin/pg_upgrade \
   -b /usr/lib/postgresql/9.5/bin \
   -B /usr/lib/postgresql/10/bin \
   -d /var/lib/postgresql/data/postgresql0 \
   -D /var/lib/postgresql/10/data/postgresql0 \
   -o ' -c config_file=/etc/postgresql/9.5/main/postgresql.conf' \
   -O ' -c config_file=/etc/postgresql/10/main/postgresql.conf' --check

Está falhando com o erro abaixo empg_upgrade_server.log

command: "/usr/lib/postgresql/9.5/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/postgresql/data/postgresql0" -o "-p 50432 -b  -c config_file=/etc/postgresql/9.5/main/postgresql.conf -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directories='/tmp'" start >> "pg_upgrade_server.log" 2>&1
waiting for server to start....2017-11-14 15:27:31 UTC [16239-1] LOG:  database system was shut down at 2017-11-14 15:16:43 UTC
2017-11-14 15:27:31 UTC [16239-2] LOG:  MultiXact member wraparound protections are now enabled
2017-11-14 15:27:31 UTC [16238-1] LOG:  database system is ready to accept connections
........................................................... stopped waiting
pg_ctl: could not start server

Alguém pode me informar o que estou fazendo de errado e como posso contornar esse erro?

EDITAR

Fiz as alterações data_directoryconforme sugerido em uma resposta. Mas agora estou recebendo o erro abaixo

*failure*
Consult the last few lines of "pg_upgrade_server.log" for
the probable cause of the failure.
Performing Consistency Checks on Old Live Server
------------------------------------------------
Checking cluster versions                                   ok
connection to database failed: could not translate host name "." to address: No address associated with hostname
Failure, exiting

Eu fiz listen_addresses='*'em ambos os postgres.conf. Mas continua com o mesmo problema.

Segue abaixo o conteúdopg_upgrade_server.log

-----------------------------------------------------------------
  pg_upgrade run on Tue Nov 14 15:22:47 2017
-----------------------------------------------------------------


-----------------------------------------------------------------
  pg_upgrade run on Tue Nov 14 15:27:30 2017
-----------------------------------------------------------------

command: "/usr/lib/postgresql/9.5/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/postgresql/data/postgresql0" -o "-p 50432 -b  -c config_file=/etc/postgresql/9.5/main/postgresql.conf -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directories='/tmp'" start >> "pg_upgrade_server.log" 2>&1
waiting for server to start....2017-11-14 15:27:31 UTC [16239-1] LOG:  database system was shut down at 2017-11-14 15:16:43 UTC
2017-11-14 15:27:31 UTC [16239-2] LOG:  MultiXact member wraparound protections are now enabled
2017-11-14 15:27:31 UTC [16238-1] LOG:  database system is ready to accept connections
........................................................... stopped waiting
pg_ctl: could not start server
Examine the log output.



-----------------------------------------------------------------
  pg_upgrade run on Tue Nov 14 15:31:35 2017
-----------------------------------------------------------------

command: "/usr/lib/postgresql/9.5/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/postgresql/data/postgresql0" -o "-p 50432 -b  -c config_file=/etc/postgresql/9.5/main/postgresql.conf -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directories='/tmp'" start >> "pg_upgrade_server.log" 2>&1
waiting for server to start....2017-11-14 15:31:35 UTC [21577-1] FATAL:  lock file "postmaster.pid" already exists
2017-11-14 15:31:35 UTC [21577-2] HINT:  Is another postmaster (PID 16238) running in data directory "/var/lib/postgresql/9.5/main"?
 stopped waiting
pg_ctl: could not start server
Examine the log output.



-----------------------------------------------------------------
  pg_upgrade run on Tue Nov 14 15:35:01 2017
-----------------------------------------------------------------

command: "/usr/lib/postgresql/9.5/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/postgresql/data/postgresql0" -o "-p 50432 -b -cconfig_file=/etc/postgresql/9.5/main/postgresql.conf -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directories='/tmp'" start >> "pg_upgrade_server.log" 2>&1
waiting for server to start....2017-11-14 15:35:01 UTC [25230-1] FATAL:  lock file "postmaster.pid" already exists
2017-11-14 15:35:01 UTC [25230-2] HINT:  Is another postmaster (PID 16238) running in data directory "/var/lib/postgresql/9.5/main"?
 stopped waiting
pg_ctl: could not start server
Examine the log output.


2017-11-14 15:40:39 UTC [16238-2] LOG:  received fast shutdown request
2017-11-14 15:40:39 UTC [16238-3] LOG:  aborting any active transactions
2017-11-14 15:40:39 UTC [16240-1] LOG:  shutting down
2017-11-14 15:40:39 UTC [16240-2] LOG:  database system is shut down

-----------------------------------------------------------------
  pg_upgrade run on Tue Nov 14 15:41:07 2017
-----------------------------------------------------------------

command: "/usr/lib/postgresql/9.5/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/postgresql/data/postgresql0" -o "-p 50432 -b  -c config_file=/etc/postgresql/9.5/main/postgresql.conf -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directories='/tmp'" start >> "pg_upgrade_server.log" 2>&1
waiting for server to start....2017-11-14 15:41:07 UTC [496-1] LOG:  database system was shut down at 2017-11-14 15:40:39 UTC
2017-11-14 15:41:07 UTC [496-2] LOG:  MultiXact member wraparound protections are now enabled
2017-11-14 15:41:07 UTC [495-1] LOG:  database system is ready to accept connections
........................................................... stopped waiting
pg_ctl: could not start server
Examine the log output.


2017-11-14 18:26:00 UTC [495-2] LOG:  received fast shutdown request
2017-11-14 18:26:00 UTC [495-3] LOG:  aborting any active transactions
2017-11-14 18:26:00 UTC [497-1] LOG:  shutting down
2017-11-14 18:26:00 UTC [497-2] LOG:  database system is shut down

-----------------------------------------------------------------
  pg_upgrade run on Wed Nov 15 03:22:53 2017
-----------------------------------------------------------------

command: "/usr/lib/postgresql/9.5/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/postgresql/data/postgresql0" -o "-p 50432 -c autovacuum=off -c autovacuum_freeze_max_age=2000000000  -c config_file=/etc/postgresql/9.5/main/postgresql.conf -c listen_addresses='' -c unix_socket_permissions=0700" start >> "pg_upgrade_server.log" 2>&1
pg_ctl: another server might be running; trying to start server anyway
waiting for server to start....2017-11-15 03:22:53 UTC [12182-1] LOG:  database system was interrupted; last known up at 2017-11-14 18:36:43 UTC
2017-11-15 03:22:53 UTC [12182-2] LOG:  database system was not properly shut down; automatic recovery in progress
2017-11-15 03:22:53 UTC [12182-3] LOG:  invalid record length at 0/170EAC8
2017-11-15 03:22:53 UTC [12182-4] LOG:  redo is not required
2017-11-15 03:22:53 UTC [12182-5] LOG:  MultiXact member wraparound protections are now enabled
2017-11-15 03:22:53 UTC [12178-1] LOG:  database system is ready to accept connections
........................................................... stopped waiting
pg_ctl: could not start server
Examine the log output.



-----------------------------------------------------------------
  pg_upgrade run on Wed Nov 15 03:26:53 2017
-----------------------------------------------------------------

command: "/usr/lib/postgresql/9.5/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/postgresql/data/postgresql0" -o "-p 50432 -c autovacuum=off -c autovacuum_freeze_max_age=2000000000  -c config_file=/etc/postgresql/9.5/main/postgresql.conf -c listen_addresses='' -c unix_socket_permissions=0700" start >> "pg_upgrade_server.log" 2>&1
pg_ctl: another server might be running; trying to start server anyway
waiting for server to start....2017-11-15 03:26:53 UTC [21689-1] FATAL:  lock file "postmaster.pid" already exists
2017-11-15 03:26:53 UTC [21689-2] HINT:  Is another postmaster (PID 12178) running in data directory "/var/lib/postgresql/9.5/main"?
 stopped waiting
pg_ctl: could not start server
Examine the log output.



-----------------------------------------------------------------
  pg_upgrade run on Wed Nov 15 03:29:13 2017
-----------------------------------------------------------------

command: "/usr/lib/postgresql/9.5/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/postgresql/data/postgresql0" -o "-p 50432 -c autovacuum=off -c autovacuum_freeze_max_age=2000000000  -c config_file=/etc/postgresql/9.5/main/postgresql.conf -c listen_addresses='' -c unix_socket_permissions=0700" start >> "pg_upgrade_server.log" 2>&1
pg_ctl: another server might be running; trying to start server anyway
waiting for server to start....2017-11-15 03:29:13 UTC [27855-1] FATAL:  lock file "postmaster.pid" already exists
2017-11-15 03:29:13 UTC [27855-2] HINT:  Is another postmaster (PID 12178) running in data directory "/var/lib/postgresql/9.5/main"?
 stopped waiting
pg_ctl: could not start server
Examine the log output.



-----------------------------------------------------------------
  pg_upgrade run on Wed Nov 15 05:55:38 2017
-----------------------------------------------------------------

command: "/usr/lib/postgresql/9.5/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/postgresql/data/postgresql0" -o "-p 50432 -c autovacuum=off -c autovacuum_freeze_max_age=2000000000  -c config_file=/etc/postgresql/9.5/main/postgresql.conf -c listen_addresses='' -c unix_socket_permissions=0700" start >> "pg_upgrade_server.log" 2>&1
pg_ctl: another server might be running; trying to start server anyway
waiting for server to start....2017-11-15 05:55:38 UTC [20731-1] FATAL:  lock file "postmaster.pid" already exists
2017-11-15 05:55:38 UTC [20731-2] HINT:  Is another postmaster (PID 19904) running in data directory "/var/lib/postgresql/data/postgresql0"?
 stopped waiting
pg_ctl: could not start server
Examine the log output.

Abaixo está a saída do grep data_directory

support@vrni-platform:~$ sudo grep data_directory /etc/postgresql/*/main/postgresql.conf
/etc/postgresql/10/main/postgresql.conf:data_directory = '/var/lib/postgresql/10/data/postgresql0'      # use data in another directory
/etc/postgresql/9.5/main/postgresql.conf:data_directory = '/var/lib/postgresql/data/postgresql0'        # use data in another directory

Abaixo está o conteúdo das configurações de locais de arquivos, conexão e autenticação de 10 postgresql.conf. Apenas os valores não comentados são mostrados

data_directory = '/var/lib/postgresql/10/data/postgresql0'              
hba_file = '/etc/postgresql/10/main/pg_hba.conf'        
ident_file = '/etc/postgresql/10/main/pg_ident.conf'    
external_pid_file = '/var/run/postgresql/10-main.pid'   
listen_addresses = '*'         
port = 5433                    
max_connections = 100          
unix_socket_directories = '/var/run/postgresql'
ssl = on
ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem'
ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key'

Abaixo está o conteúdo das configurações de locais de arquivos, conexão e autenticação de 9.5 postgresql.conf. Apenas os valores não comentados são mostrados

data_directory = '/var/lib/postgresql/data/postgresql0'         
hba_file = '/etc/postgresql/9.5/main/pg_hba.conf'       
ident_file = '/etc/postgresql/9.5/main/pg_ident.conf'   
external_pid_file = '/var/run/postgresql/9.5-main.pid'    
listen_addresses = '*'          
port = 5432                     
max_connections = 100           
unix_socket_directories = '/var/run/postgresql' 
ssl = true                              
ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem'          
ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key'

EDIÇÃO 2

Ao parar o cluster e executar o pg_upgrade, ele está falhando com o mesmo erro. Parece que pg_upgrade está começando 9.5. Abaixo está a saída

ubuntu@vrni-platform:~$ sudo service postgresql status
9.5/main (port 5432): down
10/main (port 5433): down
ubuntu@vrni-platform:~$ cd /tmp/
ubuntu@vrni-platform:/tmp$ sudo service postgresql status
9.5/main (port 5432): down
10/main (port 5433): down
ubuntu@vrni-platform:/tmp$ sudo -H -u postgres /usr/lib/postgresql/10/bin/pg_upgrade \
>    -b /usr/lib/postgresql/9.5/bin \
>    -B /usr/lib/postgresql/10/bin \
>    -d /var/lib/postgresql/data/postgresql0 \
>    -D /var/lib/postgresql/10/data/postgresql0 \
>    -o ' -c config_file=/etc/postgresql/9.5/main/postgresql.conf' \
>    -O ' -c config_file=/etc/postgresql/10/main/postgresql.conf' --check

*failure*
Consult the last few lines of "pg_upgrade_server.log" for
the probable cause of the failure.
Performing Consistency Checks on Old Live Server
------------------------------------------------
Checking cluster versions                                   ok
connection to database failed: could not translate host name "." to address: No address associated with hostname
Failure, exiting
ubuntu@vrni-platform:/tmp$ sudo service postgresql status
9.5/main (port 5432): online
10/main (port 5433): down

Abaixo está a saída das últimas linhas depg_upgrade_server.log

command: "/usr/lib/postgresql/9.5/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/postgresql/data/postgresql0" -o "-p 50432 -c autovacuum=off -c autovacuum_freeze_max_age=2000000000  -c config_file=/etc/postgresql/9.5/main/postgresql.conf -c listen_addresses='' -c unix_socket_permissions=0700" start >> "pg_upgrade_server.log" 2>&1
pg_ctl: another server might be running; trying to start server anyway
waiting for server to start....2017-11-15 06:41:37 UTC [4556-1] LOG:  database system was interrupted; last known up at 2017-11-15 03:22:53 UTC
2017-11-15 06:41:37 UTC [4556-2] LOG:  database system was not properly shut down; automatic recovery in progress
2017-11-15 06:41:37 UTC [4556-3] LOG:  invalid record length at 0/170EB38
2017-11-15 06:41:37 UTC [4556-4] LOG:  redo is not required
2017-11-15 06:41:37 UTC [4556-5] LOG:  MultiXact member wraparound protections are now enabled
2017-11-15 06:41:37 UTC [4547-1] LOG:  database system is ready to accept connections
........................................................... stopped waiting
pg_ctl: could not start server
Examine the log output.
postgresql postgresql-9.5
Martin Hope
tuk
Asked: 2017-11-11 03:53:36 +0800 CST
  • 1

Estou testando o processo de atualização do postgres para 10 de 9.5

Estou seguindo os passos mencionados neste link -

dmanna@ubuntu:~$ pg_lsclusters 
Ver Cluster Port Status Owner    Data directory               Log file
9.5 main    5432 online postgres /var/lib/postgresql/9.5/main /var/log/postgresql/postgresql-9.5-main.log
10  main    5433 online postgres /var/lib/postgresql/10/main  /var/log/postgresql/postgresql-10-main.log

sudo pg_dropcluster 10 main --stop
sudo pg_upgradecluster 9.5 main -m upgrade
sudo pg_dropcluster 9.5 main

Alguém pode me informar o seguinte

  1. Se houver uma maneira de verificar se a atualização será bem-sucedida antes de fazer a atualização real?
  2. Se eu estiver usando a opção de link na atualização, ainda posso usar o sudo pg_dropcluster 9.5 main?
postgresql