CODEWITHSUNDEEP
ruby-on-railspostgresql
Niraj Dharwal
Niraj Dharwal

Reputation: 95

PG::UnableToSend: server closed the connection unexpectedly with ruby on rails

namespace :db do
  desc 'Force close database connection'
  task hard_reset: :environment do
    Rails.logger.info "=============> Start of rake task db:hard_reset..."
    conn = ActiveRecord::Base.connection
    db_config = YAML.load_file(Rails.root.join('config', 'database.yml'))[Rails.env]
    # Terminate all connections except our current one
    # Close the connection behind us
    # conn.execute("ALTER DATABASE #{db_config['database']} WITH ALLOW_CONNECTIONS false")

    conn.execute("SELECT
                    pg_terminate_backend (pid)
                  FROM
                    pg_stat_activity
                  WHERE
                    pid <> pg_backend_pid ()
                  AND datname = '#{db_config['database']}';")

    ActiveRecord::Base.connection.close
    # Invoke a task now all connections are gone
    result = ActiveRecord::Base.clear_all_connections!
    Rails.logger.info "=============> Result on clear connection...#{result}"

    Rails.logger.info "=============> Start db:drop..."
    **Rake::Task['db:drop'].invoke**
    Rails.logger.info "=============> Finish db:drop..."
    Rails.logger.info "=============> Start db:create..."
    Rake::Task['db:create'].invoke
    Rails.logger.info "=============> Finish db:create..."
    Rails.logger.info "=============> Finish of rake task db:hard_reset..."
  end
end

I have an automated production dump restore feature, Which is working fine on staging and development, but when I am trying to run my feature on the production server then It gives me the following errors. You can check my rake task code as I have above mentioned. I have a 12.6 Postgres version. Rails 6.0.3.4

D, [2021-03-24T06:49:41.427472 #4178872] DEBUG -- :    (2.4ms)  SELECT
                    pg_terminate_backend (pid)
                  FROM
                    pg_stat_activity
                  WHERE
                    pid <> pg_backend_pid ()
                  AND datname = 'database_production';
I, [2021-03-24T06:49:56.447235 #4178872]  INFO -- : =============> Start db:drop...
D, [2021-03-24T06:49:56.472527 #4178872] DEBUG -- :    (0.7ms)  SELECT "schema_migrations"."version" FROM "schema_migrations" ORDER BY "schema_migrations"."version" ASC
D, [2021-03-24T06:49:56.478579 #4178872] DEBUG -- :    (0.5ms)  SELECT "ar_internal_metadata"."value" FROM "ar_internal_metadata" WHERE "ar_internal_metadata"."key" = $1  [["key", "environment"]]
D, [2021-03-24T06:49:56.480838 #4178872] DEBUG -- :    (0.4ms)  SELECT "schema_migrations"."version" FROM "schema_migrations" ORDER BY "schema_migrations"."version" ASC
D, [2021-03-24T06:49:56.482962 #4178872] DEBUG -- :    (0.3ms)  SELECT "ar_internal_metadata"."value" FROM "ar_internal_metadata" WHERE "ar_internal_metadata"."key" = $1  [["key", "environment"]]
D, [2021-03-24T06:49:56.484881 #4178872] DEBUG -- :    (0.4ms)  SELECT "schema_migrations"."version" FROM "schema_migrations" ORDER BY "schema_migrations"."version" ASC
D, [2021-03-24T06:49:56.486878 #4178872] DEBUG -- :    (0.3ms)  SELECT "ar_internal_metadata"."value" FROM "ar_internal_metadata" WHERE "ar_internal_metadata"."key" = $1  [["key", "environment"]]
I, [2021-03-24T06:49:56.505533 #4178635]  INFO -- : [ActiveJob] [RestoreSetupJob] [ed42fb71-cdd8-4b46-a12f-bc970fb36d77] =============> Status error_in_killing_process method... false
D, [2021-03-24T06:49:56.509494 #4178635] DEBUG -- : [ActiveJob] [RestoreSetupJob] [ed42fb71-cdd8-4b46-a12f-bc970fb36d77]    (0.9ms)  BEGIN
E, [2021-03-24T06:49:56.510113 #4178635] ERROR -- : [ActiveJob] [RestoreSetupJob] [ed42fb71-cdd8-4b46-a12f-bc970fb36d77] Error performing RestoreSetupJob (Job ID: ed42fb71-cdd8-4b46-a12f-bc970fb36d77) from Async(default) in 17250.16ms: 
**ActiveRecord::StatementInvalid (PG::UnableToSend: server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.
):**

Upvotes: 1

Views: 350

Answers (1)

Deepak Choudhary
Deepak Choudhary

Reputation: 139

Before terminating the pg process Id, you should revoke the connect privileges to avoid new connections.

REVOKE CONNECT ON DATABASE DB_NAME FROM PUBLIC, DB_USERNAME;

Upvotes: 1

Related Questions