PostgreSQL update fails from China

Product Support

AI-generated summary

A user in China is experiencing issues with a PostgreSQL update in Discourse, where the ./launcher rebuild data command fails due to connection problems with the PostgreSQL socket and apt-get update timing out. The issue appears to stem from using a China-based mirror (registry.cn-wulanchabu.aliyuncs.com) and network acceleration methods affecting access to external repositories like apt.postgresql.org. The user has attempted various troubleshooting steps, including ensuring the PostgreSQL service shuts down cleanly and using a VPN. The discussion suggests that the rebuild process fails at the apt-get update step, likely because the mirror or network configuration prevents access to required packages. The user is advised to verify connectivity to apt.postgresql.org and consider using a different mirror or network setup to resolve the issue. mwaniki helps guide the user through the process, confirming that the PostgreSQL socket error is resolved after proper shutdown, but the main issue remains the inability to update packages due to network restrictions.

1

data cannot be rebuild

./launcher rebuild data
x86_64 arch detected.
Ensuring launcher is up to date
Launcher is up-to-date
2.0.20250129-0720: Pulling from docker-hub-china/discourse-base
Digest: sha256:d798a945ca4d31c29e5d263cdaf807bafab9b5f6737be0b1b852bea0063a3b91
Status: Image is up to date for registry.cn-wulanchabu.aliyuncs.com/docker-hub-china/discourse-base:2.0.20250129-0720
registry.cn-wulanchabu.aliyuncs.com/docker-hub-china/discourse-base:2.0.20250129-0720
/usr/local/lib/ruby/gems/3.3.0/gems/pups-1.2.1/lib/pups.rb
/usr/local/bin/pups --stdin
I, [2025-02-06T04:49:43.809493 #1]  INFO -- : Reading from stdin
I, [2025-02-06T04:49:43.812252 #1]  INFO -- : Skipped missing before_code hook
I, [2025-02-06T04:49:43.812294 #1]  INFO -- : Skipped missing before_db_migrate hook
I, [2025-02-06T04:49:43.812309 #1]  INFO -- : Skipped missing after_code hook
I, [2025-02-06T04:49:43.816721 #1]  INFO -- : File > /etc/service/postgres/run  chmod: +x  chown: 
I, [2025-02-06T04:49:43.820580 #1]  INFO -- : File > /etc/service/postgres/log/run  chmod: +x  chown: 
I, [2025-02-06T04:49:43.824283 #1]  INFO -- : File > /etc/runit/3.d/99-postgres  chmod: +x  chown: 
I, [2025-02-06T04:49:43.827979 #1]  INFO -- : File > /root/install_postgres  chmod: +x  chown: 
I, [2025-02-06T04:49:43.832007 #1]  INFO -- : File > /root/upgrade_postgres  chmod: +x  chown: 
I, [2025-02-06T04:49:43.832273 #1]  INFO -- : Replacing data_directory = '/var/lib/postgresql/15/main' with data_directory = '/shared/postgres_data' in /etc/postgresql/15/main/postgresql.conf
I, [2025-02-06T04:49:43.832904 #1]  INFO -- : Replacing (?-mix:#?listen_addresses *=.*) with listen_addresses = '*' in /etc/postgresql/15/main/postgresql.conf
I, [2025-02-06T04:49:43.833313 #1]  INFO -- : Replacing (?-mix:#?synchronous_commit *=.*) with synchronous_commit = $db_synchronous_commit in /etc/postgresql/15/main/postgresql.conf
I, [2025-02-06T04:49:43.843122 #1]  INFO -- : Replacing (?-mix:#?shared_buffers *=.*) with shared_buffers = $db_shared_buffers in /etc/postgresql/15/main/postgresql.conf
I, [2025-02-06T04:49:43.843548 #1]  INFO -- : Replacing (?-mix:#?work_mem *=.*) with work_mem = $db_work_mem in /etc/postgresql/15/main/postgresql.conf
I, [2025-02-06T04:49:43.843885 #1]  INFO -- : Replacing (?-mix:#?default_text_search_config *=.*) with default_text_search_config = '$db_default_text_search_config' in /etc/postgresql/15/main/postgresql.conf
I, [2025-02-06T04:49:43.844162 #1]  INFO -- : Replacing (?-mix:#?checkpoint_segments *=.*) with checkpoint_segments = $db_checkpoint_segments in /etc/postgresql/15/main/postgresql.conf
I, [2025-02-06T04:49:43.844439 #1]  INFO -- : Replacing (?-mix:#?logging_collector *=.*) with logging_collector = $db_logging_collector in /etc/postgresql/15/main/postgresql.conf
I, [2025-02-06T04:49:43.844732 #1]  INFO -- : Replacing (?-mix:#?log_min_duration_statement *=.*) with log_min_duration_statement = $db_log_min_duration_statement in /etc/postgresql/15/main/postgresql.conf
I, [2025-02-06T04:49:43.845050 #1]  INFO -- : Replacing (?-mix:^#local +replication +postgres +peer$) with local replication postgres  peer in /etc/postgresql/15/main/pg_hba.conf
I, [2025-02-06T04:49:43.845283 #1]  INFO -- : Replacing (?-mix:^host.*all.*all.*127.*$) with host all all 0.0.0.0/0 md5 in /etc/postgresql/15/main/pg_hba.conf
I, [2025-02-06T04:49:43.845505 #1]  INFO -- : Replacing (?-mix:^host.*all.*all.*::1\/128.*$) with host all all ::/0 md5 in /etc/postgresql/15/main/pg_hba.conf
I, [2025-02-06T04:49:43.845723 #1]  INFO -- : > if [ -f /root/install_postgres ]; then
  /root/install_postgres && rm -f /root/install_postgres
elif [ -e /shared/postgres_run/.s.PGSQL.5432 ]; then
  socat /dev/null UNIX-CONNECT:/shared/postgres_run/.s.PGSQL.5432 || exit 0 && echo postgres already running stop container ; exit 1
fi

2025/02/06 04:49:45 socat[33] E connect(, AF=1 "/shared/postgres_run/.s.PGSQL.5432", 36): Connection refused
initdb: warning: enabling "trust" authentication for local connections
initdb: hint: 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.
2

Your logs appear to be incomplete but the snippet below can provide a hint.

socat[33] E connect(, AF=1 “/shared/postgres_run/.s.PGSQL.5432”, 36): Connection refused

Are you also seeing the error below much lower in your log output?

The source cluster was not shut down cleanly

If so, you can try following these instructions.

3

No containers are running

4

Are you seeing any errors from that first step?

5

I used the data.yml and web_only.yml methods

6

You can still follow the same instructions, but replace any occurrences of app or standalone with data.

7

Still not connected

$ ./launcher enter data
x86_64 arch detected.
root@sober-data:/# export SVWAIT=300
sv stop nginx
sv stop unicorn
sv stop postgres
exit
fail: nginx: unable to change to service directory: file does not exist
fail: unicorn: unable to change to service directory: file does not exist
ok: down: postgres: 0s, normally up
logout
8

This implies you were connected to the container. (Just ensure that the web_only container remains stopped during the rebuild of the data container.)

ok: down: postgres: 0s, normally up

This is the output that matters. It means postgres has now shut down cleanly and you can continue with the next steps.

9 
 ./launcher enter  data
x86_64 arch detected.
root@sober-data:/# sv stop postgres
ok: down: postgres: 0s, normally up
root@sober-data:/# exit
logout
root@sober:/var/discourse$ ./launcher rebuild  data
x86_64 arch detected.
Ensuring launcher is up to date
Launcher is up-to-date
Stopping old container
+ /usr/bin/docker stop -t 600 data
data
2.0.20250129-0720: Pulling from docker-hub-china/discourse-base
Digest: sha256:d798a945ca4d31c29e5d263cdaf807bafab9b5f6737be0b1b852bea0063a3b91
Status: Image is up to date for registry.cn-wulanchabu.aliyuncs.com/docker-hub-china/discourse-base:2.0.20250129-0720
registry.cn-wulanchabu.aliyuncs.com/docker-hub-china/discourse-base:2.0.20250129-0720
/usr/local/lib/ruby/gems/3.3.0/gems/pups-1.2.1/lib/pups.rb
/usr/local/bin/pups --stdin
I, [2025-02-06T06:19:20.379635 #1]  INFO -- : Reading from stdin
I, [2025-02-06T06:19:20.380959 #1]  INFO -- : Skipped missing before_code hook
I, [2025-02-06T06:19:20.380976 #1]  INFO -- : Skipped missing before_db_migrate hook
I, [2025-02-06T06:19:20.380983 #1]  INFO -- : Skipped missing after_code hook
I, [2025-02-06T06:19:20.383923 #1]  INFO -- : File > /etc/service/postgres/run  chmod: +x  chown: 
I, [2025-02-06T06:19:20.386435 #1]  INFO -- : File > /etc/service/postgres/log/run  chmod: +x  chown: 
I, [2025-02-06T06:19:20.389343 #1]  INFO -- : File > /etc/runit/3.d/99-postgres  chmod: +x  chown: 
I, [2025-02-06T06:19:20.391719 #1]  INFO -- : File > /root/install_postgres  chmod: +x  chown: 
I, [2025-02-06T06:19:20.394375 #1]  INFO -- : File > /root/upgrade_postgres  chmod: +x  chown: 
I, [2025-02-06T06:19:20.394471 #1]  INFO -- : Replacing data_directory = '/var/lib/postgresql/15/main' with data_directory = '/shared/postgres_data' in /etc/postgresql/15/main/postgresql.conf
I, [2025-02-06T06:19:20.394742 #1]  INFO -- : Replacing (?-mix:#?listen_addresses *=.*) with listen_addresses = '*' in /etc/postgresql/15/main/postgresql.conf
I, [2025-02-06T06:19:20.394972 #1]  INFO -- : Replacing (?-mix:#?synchronous_commit *=.*) with synchronous_commit = $db_synchronous_commit in /etc/postgresql/15/main/postgresql.conf
I, [2025-02-06T06:19:20.395126 #1]  INFO -- : Replacing (?-mix:#?shared_buffers *=.*) with shared_buffers = $db_shared_buffers in /etc/postgresql/15/main/postgresql.conf
I, [2025-02-06T06:19:20.395261 #1]  INFO -- : Replacing (?-mix:#?work_mem *=.*) with work_mem = $db_work_mem in /etc/postgresql/15/main/postgresql.conf
I, [2025-02-06T06:19:20.395400 #1]  INFO -- : Replacing (?-mix:#?default_text_search_config *=.*) with default_text_search_config = '$db_default_text_search_config' in /etc/postgresql/15/main/postgresql.conf
I, [2025-02-06T06:19:20.395510 #1]  INFO -- : Replacing (?-mix:#?checkpoint_segments *=.*) with checkpoint_segments = $db_checkpoint_segments in /etc/postgresql/15/main/postgresql.conf
I, [2025-02-06T06:19:20.395622 #1]  INFO -- : Replacing (?-mix:#?logging_collector *=.*) with logging_collector = $db_logging_collector in /etc/postgresql/15/main/postgresql.conf
I, [2025-02-06T06:19:20.395723 #1]  INFO -- : Replacing (?-mix:#?log_min_duration_statement *=.*) with log_min_duration_statement = $db_log_min_duration_statement in /etc/postgresql/15/main/postgresql.conf
I, [2025-02-06T06:19:20.395845 #1]  INFO -- : Replacing (?-mix:^#local +replication +postgres +peer$) with local replication postgres  peer in /etc/postgresql/15/main/pg_hba.conf
I, [2025-02-06T06:19:20.395930 #1]  INFO -- : Replacing (?-mix:^host.*all.*all.*127.*$) with host all all 0.0.0.0/0 md5 in /etc/postgresql/15/main/pg_hba.conf
I, [2025-02-06T06:19:20.396007 #1]  INFO -- : Replacing (?-mix:^host.*all.*all.*::1\/128.*$) with host all all ::/0 md5 in /etc/postgresql/15/main/pg_hba.conf
I, [2025-02-06T06:19:20.396096 #1]  INFO -- : > if [ -f /root/install_postgres ]; then
  /root/install_postgres && rm -f /root/install_postgres
elif [ -e /shared/postgres_run/.s.PGSQL.5432 ]; then
  socat /dev/null UNIX-CONNECT:/shared/postgres_run/.s.PGSQL.5432 || exit 0 && echo postgres already running stop container ; exit 1
fi

initdb: warning: enabling "trust" authentication for local connections
initdb: hint: 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.
10

Is the rebuild stuck at this point? If so, cancel it and run this instead then post the results.

./launcher start data
./launcher enter data
psql --version
apt-get update
11

Thank you very much. Let me go back to the docker version and postgres.template.yml

./launcher rebuild data
x86_64 arch detected.
Ensuring launcher is up to date
Launcher is up-to-date
Stopping old container
+ /usr/bin/docker stop -t 600 data
data
2.0.20250129-0720: Pulling from docker-hub-china/discourse-base
Digest: sha256:d798a945ca4d31c29e5d263cdaf807bafab9b5f6737be0b1b852bea0063a3b91
Status: Image is up to date for registry.cn-wulanchabu.aliyuncs.com/docker-hub-china/discourse-base:2.0.20250129-0720
registry.cn-wulanchabu.aliyuncs.com/docker-hub-china/discourse-base:2.0.20250129-0720
/usr/local/lib/ruby/gems/3.3.0/gems/pups-1.2.1/lib/pups.rb
/usr/local/bin/pups --stdin
I, [2025-02-06T07:02:42.768651 #1]  INFO -- : Reading from stdin
I, [2025-02-06T07:02:42.769907 #1]  INFO -- : Skipped missing before_code hook
I, [2025-02-06T07:02:42.769923 #1]  INFO -- : Skipped missing before_db_migrate hook
I, [2025-02-06T07:02:42.769933 #1]  INFO -- : Skipped missing after_code hook
I, [2025-02-06T07:02:42.772767 #1]  INFO -- : File > /etc/service/postgres/run  chmod: +x  chown: 
I, [2025-02-06T07:02:42.775230 #1]  INFO -- : File > /etc/service/postgres/log/run  chmod: +x  chown: 
I, [2025-02-06T07:02:42.777690 #1]  INFO -- : File > /etc/runit/3.d/99-postgres  chmod: +x  chown: 
I, [2025-02-06T07:02:42.779645 #1]  INFO -- : File > /root/install_postgres  chmod: +x  chown: 
I, [2025-02-06T07:02:42.781825 #1]  INFO -- : File > /root/upgrade_postgres  chmod: +x  chown: 
I, [2025-02-06T07:02:42.781974 #1]  INFO -- : Replacing data_directory = '/var/lib/postgresql/15/main' with data_directory = '/shared/postgres_data' in /etc/postgresql/15/main/postgresql.conf
I, [2025-02-06T07:02:42.782261 #1]  INFO -- : Replacing (?-mix:#?listen_addresses *=.*) with listen_addresses = '*' in /etc/postgresql/15/main/postgresql.conf
I, [2025-02-06T07:02:42.782427 #1]  INFO -- : Replacing (?-mix:#?synchronous_commit *=.*) with synchronous_commit = $db_synchronous_commit in /etc/postgresql/15/main/postgresql.conf
I, [2025-02-06T07:02:42.782551 #1]  INFO -- : Replacing (?-mix:#?shared_buffers *=.*) with shared_buffers = $db_shared_buffers in /etc/postgresql/15/main/postgresql.conf
I, [2025-02-06T07:02:42.782661 #1]  INFO -- : Replacing (?-mix:#?work_mem *=.*) with work_mem = $db_work_mem in /etc/postgresql/15/main/postgresql.conf
I, [2025-02-06T07:02:42.782762 #1]  INFO -- : Replacing (?-mix:#?default_text_search_config *=.*) with default_text_search_config = '$db_default_text_search_config' in /etc/postgresql/15/main/postgresql.conf
I, [2025-02-06T07:02:42.782877 #1]  INFO -- : Replacing (?-mix:#?checkpoint_segments *=.*) with checkpoint_segments = $db_checkpoint_segments in /etc/postgresql/15/main/postgresql.conf
I, [2025-02-06T07:02:42.782974 #1]  INFO -- : Replacing (?-mix:#?logging_collector *=.*) with logging_collector = $db_logging_collector in /etc/postgresql/15/main/postgresql.conf
I, [2025-02-06T07:02:42.783076 #1]  INFO -- : Replacing (?-mix:#?log_min_duration_statement *=.*) with log_min_duration_statement = $db_log_min_duration_statement in /etc/postgresql/15/main/postgresql.conf
I, [2025-02-06T07:02:42.783180 #1]  INFO -- : Replacing (?-mix:^#local +replication +postgres +peer$) with local replication postgres  peer in /etc/postgresql/15/main/pg_hba.conf
I, [2025-02-06T07:02:42.783258 #1]  INFO -- : Replacing (?-mix:^host.*all.*all.*127.*$) with host all all 0.0.0.0/0 md5 in /etc/postgresql/15/main/pg_hba.conf
I, [2025-02-06T07:02:42.783334 #1]  INFO -- : Replacing (?-mix:^host.*all.*all.*::1\/128.*$) with host all all ::/0 md5 in /etc/postgresql/15/main/pg_hba.conf
I, [2025-02-06T07:02:42.783416 #1]  INFO -- : > if [ -f /root/install_postgres ]; then
  /root/install_postgres && rm -f /root/install_postgres
elif [ -e /shared/postgres_run/.s.PGSQL.5432 ]; then
  socat /dev/null UNIX-CONNECT:/shared/postgres_run/.s.PGSQL.5432 || exit 0 && echo postgres already running stop container ; exit 1
fi

initdb: warning: enabling "trust" authentication for local connections
initdb: hint: 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.
12

2.0.20250129-0720: Pulling from docker-hub-china/discourse-base

Can you confirm whether your server is in China?

13

Mirror is no problem, this is the mirror China network acceleration method， otherwise the mirror has no network speed

14

I moved your queries to a new topic. I believe this is related to running the update from China.

Based on your log output, the rebuild seems to fail at apt-get update. I would recommend using a VPN to connect your server to the apt repositories.

16

I also do a Chinese network acceleration process for apt-get update. I would like to know which software is updated mainly for apt-get update

I’d rather know what the root cause is that /shared/postgres_run/.s.PGSQL.5432 cannot connect

17

Are you able to reach the repository below from your server?

curl -v https://apt.postgresql.org/pub/repos/apt/

The repository is needed to install the old PostgreSQL packages used during the update.

This is typically caused when the database was not shut down properly. We had fixed it earlier when you started and stopped the data container. The error is not seen in your latest log output.

18

You mean the PostgreSQL version is too old, causing problems. This upgrade can be integrated into the Discourse/base processing in the mirror. I use the mirror’s Discourse/base: 2.0.20250129-0720.

Is there any problem with my understanding? What is the version number of the PostgreSQL that works correctly and the version number of the PostgreSQL that has issues?

Or perhaps the issue lies with the fact that the mirror I am using is too recent, causing problems with the data build. I need to construct the data using the mirror Discourse/base: 2.0.20250114-0014 to accommodate the PostgreSQL upgrade.

19

I got the upgrade PostgreSQL15

Templates need to be used in sequence

postgres.13.template.yml
postgres.15.template.yml
postgres.template.yml

postgres.13.template.yml, postgres.15.template.yml file China network acceleration configuration

run:
  - exec: cat /etc/os-release && psql --version
  - exec: |
      if [ -f /etc/apt/sources.list ]; then
        sudo sed -i 's/deb.debian.org/mirrors.ustc.edu.cn/g' /etc/apt/sources.list
      else
        echo "deb http://mirrors.ustc.edu.cn/debian bookworm main contrib non-free non-free-firmware" | sudo tee /etc/apt/sources.list
        echo "deb http://mirrors.ustc.edu.cn/debian bookworm-updates main contrib non-free non-free-firmware" | sudo tee -a /etc/apt/sources.list
      fi
  - exec: |
      if [ -f /etc/apt/sources.list.d/debian.sources ]; then
        sudo sed -i 's/deb.debian.org/mirrors.ustc.edu.cn/g' /etc/apt/sources.list.d/debian.sources
      else
        sudo bash -c 'cat > /etc/apt/sources.list.d/debian.sources <<EOF
        URIs: http://mirrors.ustc.edu.cn/debian
        Suites: bookworm bookworm-updates
        Components: main contrib non-free non-free-firmware
        Signed-By: /usr/share/keyrings/debian-archive-keyring.gpg

        Types: deb
        URIs: http://mirrors.ustc.edu.cn/debian-security
        Suites: bookworm-security
        Components: main contrib non-free non-free-firmware
        Signed-By: /usr/share/keyrings/debian-archive-keyring.gpg
        EOF'
      fi
  - exec: cat /etc/apt/sources.list && cat /etc/apt/sources.list.d/debian.sources