Обсуждение: BUG #14356: "FATAL: the database system is starting up" error occurs to queries after PostgreSQL server start
VGhlIGZvbGxvd2luZyBidWcgaGFzIGJlZW4gbG9nZ2VkIG9uIHRoZSB3ZWJz aXRlOgoKQnVnIHJlZmVyZW5jZTogICAgICAxNDM1NgpMb2dnZWQgYnk6ICAg ICAgICAgIE3DoXJ0b24gS2FtcsOhcwpFbWFpbCBhZGRyZXNzOiAgICAgIHhy ZW5zaXNAZ21haWwuY29tClBvc3RncmVTUUwgdmVyc2lvbjogOS4zLjE0Ck9w ZXJhdGluZyBzeXN0ZW06ICAgVWJ1bnR1IDE0LjA0LjUgTFRTLCBUcnVzdHkg VGFocgpEZXNjcmlwdGlvbjogICAgICAgIAoKQWZ0ZXIgdGhlIHN0YXJ0dXAg b2YgdGhlIFBvc3RncmVTUUwgc2VydmVyIHRoZXJlIGlzIGEgc3RhdGUgd2hl biBpdCBhY2NlcHRzCmluY29taW5nIGNvbm5lY3Rpb25zIGJ1dCBnZW5lcmF0 ZXMgIkZBVEFMOiAgdGhlIGRhdGFiYXNlIHN5c3RlbSBpcyBzdGFydGluZwp1 cCIgYXMgcmVzdWx0cyBmb3IgcnVuIHF1ZXJpZXMuDQoNCkkgaGF2ZSB3cml0 dGVuIGEgcG9zdGdyZXNxbF85LjMuMTRfdGVzdC5zaCBzY3JpcHQgdGhhdCBp bnN0YWxscyB0aGUgcmVxdWlyZWQKZW52aXJvbm1lbnQgYW5kIHJlcHJvZHVj ZXMgdGhlIGJ1Zy4NCg0KcG9zdGdyZXNxbF85LjMuMTRfdGVzdC5zaCBhbmQg aXRzIHJlcXVpcmVkIGZpbGVzIGNhbiBiZSBkb3dubG9hZGVkIGZyb206Cmh0 dHBzOi8vZ2l0aHViLmNvbS8xZm9yaW50b3MvcG9zdGdyZXNxbF9qZGJjX3Rl c3QvdHJlZS9tYXN0ZXIvcG9zdGdyZXNfYnVnX3JlcG9ydA0KDQpUaGUgc2Ny aXB0IHdhcyB0ZXN0ZWQgb246DQogICAgLSBIb3N0IE9TOiBVYnVudHUgMTYu MDQuMSBMVFMgKFhlbmlhbCBYZXJ1cykgNjRiaXQNCiAgICAtIERvY2tlciB2 ZXJzaW9uIGF0IHRoZSB0aW1lIG9mIHRlc3Rpbmc6IDEuMTIuMSwgYnVpbGQg MjNjZjYzOC4NCiAgICAtIGxzaHcgb3V0cHV0OgpodHRwczovL2dpdGh1Yi5j b20vMWZvcmludG9zL3Bvc3RncmVzcWxfamRiY190ZXN0L2Jsb2IvbWFzdGVy L3Bvc3RncmVzX2J1Z19yZXBvcnQvbHNod19vdXRwdXQudHh0DQoNCkluZm9y bWF0aW9uIG9uIHRoZSB0ZXN0IGVudmlyb25tZW50IGl0IGNyZWF0ZXM6DQog ICAgLSBjb250YWluZXIgT1M6ICJVYnVudHUgMTQuMDQuNSBMVFMsIFRydXN0 eSBUYWhyIg0KICAgIC0ga2VybmVsOiBMaW51eCA0LjQuMC0zOC1nZW5lcmlj DQogICAgLSBwb3N0Z3Jlc3FsIHNlcnZlciB2ZXJzaW9uIChwc3FsIC1jICJz ZWxlY3QgdmVyc2lvbigpOyIpOiBQb3N0Z3JlU1FMCjkuMy4xNCBvbiB4ODZf NjQtdW5rbm93bi1saW51eC1nbnUsIGNvbXBpbGVkIGJ5IGdjYyAoVWJ1bnR1 CjQuOC40LTJ1YnVudHUxfjE0LjA0LjMpIDQuOC40LCA2NC1iaXQNCiAgICAt IHBzcWwgY2xpZW50IHZlcnNpb24gKHBzcWwgLS12ZXJzaW9uKTogcHNxbCAo UG9zdGdyZVNRTCkgOS41LjQNCiAgICAtIFBvc3RncmVTUUwgY29uZmlndXJh dGlvbiBmaWxlIHVzZWQgaW4gdGhlIGNvbnRhaW5lcjoKaHR0cHM6Ly9naXRo dWIuY29tLzFmb3JpbnRvcy9wb3N0Z3Jlc3FsX2pkYmNfdGVzdC9ibG9iL21h c3Rlci9wb3N0Z3Jlc19idWdfcmVwb3J0L3Bvc3RncmVzcWwuY29uZg0KDQpS ZXF1aXJlZCBmaWxlcyBpbiB0aGUgc2FtZSBmb2xkZXIgYXMgdGhlIHJ1biBz Y3JpcHQ6DQogICAgLSBpbnN0YWxsX2RvY2tlci5zaA0KICAgIC0gRG9ja2Vy ZmlsZQ0KICAgIC0gcG9zdGdyZXNxbC5jb25mDQogICAgLSBnZW5lcmF0b3Jf c2NyaXB0LnB5DQogICAgLSB0ZXN0X2RiLnNoDQogICAgLSBqYXJzL3Bvc3Rn cmVzLmRiY2xpZW50Lmphcg0KICAgIC0gamFycy9wb3N0Z3Jlc3FsLWpkYmM0 LTkuMi5qYXINCiAgICAtIGphcnMvY29tbW9ucy1jc3YtMS40Lmphcg0KDQpP dmVydmlldyBvZiB0aGlzIHNjcmlwdDoNCiAgICAtIENoZWNrcyBkb2NrZXIg YW5kIGphdmEgaW5zdGFsbGF0aW9uIGFuZCBvZmZlcnMgdGhlaXIKdXBkYXRl L2luc3RhbGx0aW9uLg0KICAgIC0gQ3JlYXRlcyBhbmQgcnVucyBkb2NrZXIg Y29udGFpbmVyIGZvciB0aGUgcG9zdGdyZXNxbCBkYXRhYmFzZSBzZXJ2ZXIK dXNpbmcgdGhlIHJlcXVpcmVkIGRvY2tlciBpbWFnZSBmaWxlLg0KICAgIC0g T25jZSB0aGUgY29udGFpbmVyIGFuZCB0aGUgZGF0YWJhc2UgaXMgc2V0IHVw IGl0IGluc2VydHMgZHVtbXkgZGF0YQppbnRvIHRoZSBkYXRhYmFzZS4NCiAg ICAtIFRoZW4gaXQgY3JlYXRlcyBhIGxvb3A6DQogICAgICAgIC0gc3RvcHMg dGhlIGRhdGFiYXNlDQogICAgICAgIC0gc3RhcnRzIHRoZSBkYXRhYmFzZSBh bmQgcnVucyBxdWVyaWVzIHVzaW5nIGEgSkRCQyBhcHBsaWNhdGlvbiBhdAp0 aGUgc2FtZSB0aW1lDQogICAgLSBBdCB0aGUgZW5kIGl0IGNvcGllcyBkYXRh YmFzZSBsb2dzIGludG8gdGhlIGxvZ3MvcGdfbG9nIGZvbGRlcg0KICAgIC0g VGhlIEpEQkMgYXBwbGljYXRpb24gY3JlYXRlcyBsb2dzDQoNCk5vdGU6DQog ICAgLSBsb2dnaW5nIG9mIHBvc3RncmVzcWwgaXMgc2V0IHRvIGxldmVsICJk ZWJ1ZzUiIHdoaWNoIChpbiB0aGlzIGN1cnJlbnQKc2V0dXApIGdlbmVyYXRl cyBhcm91bmQgMUdCIG9mIGxvZ3Mgd2l0aGluIHRoZSBjb250YWluZXIuDQog ICAgLSB0aGlzIGNhbiBiZSBsb3dlcmVkIGJ5IG1vZGlmeWluZyB0aGUgcG9z dGdyZXNxbC5jb25mIGZpbGUgaW4gdGhlIHNhbWUKZm9sZGVyIGFzIHRoZSBz Y3JpcHQgaXMgdGhlbiBydW5uaW5nIHBvc3RncmVzcWxfOS4zLjE0X3Rlc3Qu c2gNCiAgICAtIHBvc3RncmVzcWxfOS4zLjE0X3Rlc3Quc2ggbW9zdCBsaWtl bHkgcmVxdWlyZXMgc3VwZXJ1c2VyIHByaXZpbGVnZXMgdG8Kd29yaw0KICAg IC0gdG90YWwgcnVuIG9mIHRoZSBzY3JpcHQgbWF5IHRha2UgdXAgdG8gMzAg bWludXRlcyBiZWNhdXNlIG9mIHRoZSBsb3cKaGFyZHdhcmUgcmVzb3VyY2Vz IHRoZSBkb2NrZXIgY29udGFpbmVyIGdldHMgKHNvIGFzIHRvIHJlcHJvZHVj ZSBiZXN0IHRoZQpidWcgdGhhdCBvY2N1cnMgb24gImxvdyByZXNvdXJjZSIg aGFyZHdhcmVzKQ0KICAgICAgICAtIHRoaXMgdGltZSBjYW4gYmUgcmVkdWNl ZCByZWRlZmluaW5nICJjcHVRdW90YSIgaW4gdGhlIHNjcmlwdCBieQptb2Rp ZnlpbmcgdGhlIGZvbGxvd2luZyBsaW5lOg0KICAgICAgICAgICAgLSAibGV0 IGNwdVF1b3RhPTUwMDAwMDAwLyRtYXhDbG9ja1NwZWVkIiB0byAibGV0IGNw dVF1b3RhPVkiCndoZXJlIFkgaXMgWT1YKjEwMDAgd2hlcmUgWCBpcyB0aGUg cGVyY2VudGFnZSBvZiBtYXggQ1BVIHNwZWVkIHdlIHdhbnQgdG8KZ2l2ZSB0 aGUgZG9ja2VyIGNvbnRhaW5lcg0KICAgICAgICAgICAgLSB0aGUgaGlnaGVy IHRoaXMgcGVyY2VudGFnZSBpcyB0aGUgbGVzcyBsaWtlbHkgaXQgd2lsbCBi ZSB0bwpyZXByb2R1Y2UgdGhlIGJ1Zw0KDQpEZXRhaWxzIG9mIHRoaXMgc2Ny aXB0Og0KICAgIDEuIGluc3RhbGxzL3VwZGF0ZXMgRG9ja2VyIGNvbnRhaW5l cml6YXRpb24gcGxhdGZvcm0sIEphdmEgNyAoYWZ0ZXIgdXNlcgpjb25maXJt YXRpb24pDQogICAgMi4gc3RvcHMgQUxMIHByZXZpb3VzbHkgc3RhcnRlZCBj b250YWluZXJzIGluIGNhc2UgdGhleSBleGlzdHMNCiAgICAzLiByZW1vdmVz IGdlbmVyYXRlZCBjb250ZW50IG9mIHByZXZpb3VzIHJ1bnMgb2YgdGhlIHNj cmlwdA0KICAgIDQuIGJ1aWxkcyBhIERvY2tlciBpbWFnZSB1c2luZyB0aGlz IERvY2tlcmZpbGUgW2dpdGh1YiBsaW5rXSB3aXRoCiJ1YnVudHVfMTQuMDQv cGdfOS4zLjE0OmxhdGVzdCIgdGFnDQogICAgNS4gKHVzaW5nIGxzY3B1KSBj YWxjdWxhdGVzIHRoZSBDUFUgcXVvdGEgdG8gYmUgZ3JhbnRlZCB0byB0aGUg RG9ja2VyCmNvbnRhaW5lciB0byBhY2hpZXZlIH40MDBNSHogY2xvY2sgc3Bl ZWQNCiAgICA2LiBjcmVhdGVzIGFuZCBzdGFydHMgYSBjb250YWluZXIgd2l0 aCB0aGUgbmFtZSAicG9zdGdyZXNfOS4zLjE0IiB0aGF0CmhhcyAxIENQVSBj b3JlIGFuZCB0aGUgY2FsY3VsYXRlZCBDUFUgcXVvdGENCiAgICA3LiBzdGFy dHMgdGhlIFBvc3RncmVTUUwgc2VydmVyIGluIHRoZSBjb250YWluZXINCiAg ICA4LiBydW5lIHRoZSAiZ2VuZXJhdG9yX3NjcmlwdC5weSIgc2NyaXB0IHdo aWNoOg0KICAgICAgICA4LjEuIGdlbmVyYXRlcyB0aGUgU1FMIGZpbGVzIGZv ciBpbnNlcnRpbmcgZHVtbXkgZGF0YSBpbnRvIHRoZQpkYXRhYmFzZSAoWzUg dGFibGVzXSBYIFtpZCArIDEwIGNvbHVtbnMgKHZhcmNoYXIoMTAwKSldIFgg WzE4MGsgcm93c10sCihleGFtcGxlOiBbZ2l0aHViIGxpbmtdKSkNCiAgICAg ICAgOC4yLiBnZW5lcmF0ZXMgQ1NWIGZpbGVzIGZvciB0aGUgSkRCQyBhcHBs aWNhdGlvbiAoc2VlIDkuMi5iLjIpDQogICAgICAgIDguMy4gZXhlY3V0ZXMg YWxsIFNRTCBzY3JpcHRzIG9uZS1ieS1vbmUgd2hpY2ggd2VyZSBnZW5lcmF0 ZWQgaW4KOC4xLCB1c2luZyB0aGUgZm9sbG93aW5nIGNvbW1hbmQ6IHBzcWwg LWggPGNvbnRhaW5lcl9JUD4gLVUgcG9zdGdyZXMgLWYKPHNxbF9zY3JpcHRf cGF0aD4gLTENCiAgICA5LiBydW5zIHRoZSB0ZXN0X2RiLnNoIHNjcmlwdCBm b3IgMzAwIHNlY29uZHMgd2hpY2g6DQogICAgICAgIDkuMS4gc3RvcHMgdGhl IFBvc3RncmVTUUwgc2VydmVyIGluIHRoZSBjb250YWluZXINCiAgICAgICAg OS4yLiBhKSBhbmQgYikgYXJlIGV4ZWN1dGVkIHBhcmFsbGVsZWQ6DQogICAg ICAgICAgICBhKSBzdGFydHMgdGhlIFBvc3RncmVTUUwgc2VydmVyIGluIHRo ZSBjb250YWluZXINCiAgICAgICAgICAgIGIpIHJ1bnMgdGhlIEpEQkMgYXBw bGljYXRpb24gd2hpY2g6DQogICAgICAgICAgICAgICAgOS4yLmIuMSBydW5z ICJTRUxFQ1QgY291bnQoKikgQVMgcm93X2NvdW50IEZST00gdGFibGVOIgpO PTEuLjUgdW50aWwgYWxsIDUgdGFibGVzIHJldHVybiBhIDA+IHJvd19jb3Vu dA0KICAgICAgICAgICAgICAgIDkuMi5iLjIgY2hlY2tzIGFuZCBjb21wYXJl cyBvbmUgcmFuZG9tIHJvdyBmcm9tIGFsbCB0YWJsZXMKaW4gdGhlIGRhdGFi YXNlIHRvIHRoZSBjb3JyZXNwb25kaW5nIHZhbHVlcyBjb250YWluZWQgYnkg dGhlIGdlbmVyYXRlZCBDU1YKZmlsZXMgKGluIDguMikgdG8gdmVyaWZ5IHRo ZWlyIGNvcnJlY3RuZXNzDQogICAgICAgIDkuMy4gY2hlY2tzIHRoZSBleGl0 IGNvZGUgb2YgdGhlIEpEQkMgYXBwbGljYXRpb24sIGlmIHRoZSBleGl0IGNv ZGUKZXF1YWxzIDEgdGhlbiB0aGUgZGF0YSBjb21wYXJlZCBpbiA5LjIuYi4y IGRvIG5vdCBtYXRjaA0KICAgICAgICA5LjQuIEdPVE8gOS4xLg0KICAgIDEw LiBjb3BpZXMgdGhlIGdlbmVyYXRlZCBsb2dzIGJ5IHRoZSBQb3N0Z3JlU1FM IHNlcnZlciBmcm9tIHRoZQpjb250YWluZXIgdG8gdGhlIDxkaXJlY3Rvcnkg d2hlcmUgdGhlIHNjcmlwdCBpcyBydW4+L2xvZ3MvcGdfbG9nIGZvbGRlcg0K ICAgIDExLiByZXN0YXJ0cyB0aGUgUG9zdGdyZVNRTCBzZXJ2ZXIgaW4gdGhl IGNvbnRhaW5lcg0KDQogICAgQWZ0ZXIgcnVubmluZyB0aGlzIHNjcmlwdCB0 aGVyZSBhcmUgMiBmb2xkZXJzIGluIHRoZSBsb2dzIGZvbGRlcg0KICAgICAg ICAtIGpkYmM6IGNvbnRhaW5pbmcgZ2VuZXJhdGVkIGxvZ3Mgb2YgdGhlIEpE QkMgYXBwbGljYXRpb24NCiAgICAgICAgLSBwZ19sb2c6IGNvbnRhaW5pbmcg bG9ncyBjb3BpZWQgZnJvbSB0aGUgUG9zdGdyZVNRTCBzZXJ2ZXINCg0KICAg IEFtb25nIHRoZSBKREJDIGxvZ3MgdGhlcmUgaXMgYSBoaWdoIGNoYW5jZSBv ZiBzZWVpbmcgIkZBVEFMOiB0aGUKZGF0YWJhc2Ugc3lzdGVtIGlzIHN0YXJ0 aW5nIHVwIiBhcyBhbiBleGNlcHRpb24gZHVyaW5nIHRoZSBydW5zIG9mCnF1 ZXJpZXMuDQoNClRoZSBKREJDIGFwcGxpY2F0aW9uOg0KICAgIC0gcHJvamVj dCBjYW4gYmUgZm91bmQgdmlhIHRoaXMgbGluazoKaHR0cHM6Ly9naXRodWIu Y29tLzFmb3JpbnRvcy9wb3N0Z3Jlc3FsX2pkYmNfdGVzdC90cmVlL21hc3Rl ci9qYXZhL3Bvc3RncmVzLmRiY2xpZW50DQogICAgLSBwcm9qZWN0IHdhcyBj cmVhdGVkIHdpdGggRWNsaXBzZSBMdW5hIDMyYml0DQogICAgLSBidWlsZCB3 aXRoIEFwYWNoZSBNYXZlbiAzLjMuOSAoY29tbWFuZCBpbiBwcm9qZWN0IHJv b3Q6IG12biBjbGVhbgppbnN0YWxsKQ0KICAgIC0gcmVxdWlyZXMgSmF2YSAx LjcNCg0KMiBjb25uZWN0aW5nIHF1ZXN0aW9uczoNCiAgICAtIElzIHRoZXJl IGEga25vd24gaW50ZXJmYWNlIHRvIGNoZWNrIGlmIHRoZSBkYXRhYmFzZSBp cyB1cCBhbmQgZnVsbHkKZnVuY3Rpb25hbCBhbmQgcmVhZHkgZm9yIHF1ZXJp ZXM/DQogICAgLSBJcyBpdCBwb3NzaWJsZSB0aGVvcmV0aWNhbGx5IHRoYXQg dGhlIHJlc3VsdCBvZiBhIHF1ZXJ5IGlzIGluY29ycmVjdAphbmQgZG9lcyBu b3QgbWF0Y2ggdGhlIGFjdHVhbCBkYXRhIGluIHRoZSBkYXRhYmFzZT8NCgoK
xrensis@gmail.com writes:
> After the startup of the PostgreSQL server there is a state when it accepts
> incoming connections but generates "FATAL: the database system is starting
> up" as results for run queries.
This is not a bug, it's expected behavior.
> - Is there a known interface to check if the database is up and fully
> functional and ready for queries?
libpq's PQping() is a possibility.
regards, tom lane
On 10/05/2016 05:25 PM, xrensis@gmail.com wrote: > After the startup of the PostgreSQL server there is a state when it accepts > incoming connections but generates "FATAL: the database system is starting > up" as results for run queries. That's not a bug, that's the way it's designed to work. > - Is there a known interface to check if the database is up and fully > functional and ready for queries? The simplest way is to run a query with e.g. "psql -c 'select 1'", and see if it works. Note that you can also use "pg_ctl start -w" to have pg_ctl wait for the database to fully start up, before it returns. > - Is it possible theoretically that the result of a query is incorrect > and does not match the actual data in the database? No. (Assuming there are no bugs, hardware failure, etc, of course). - Heikki
On Thu, Oct 6, 2016 at 8:04 PM, Heikki Linnakangas <hlinnaka@iki.fi> wrote: > On 10/05/2016 05:25 PM, xrensis@gmail.com wrote: >> - Is there a known interface to check if the database is up and fully >> functional and ready for queries? > > The simplest way is to run a query with e.g. "psql -c 'select 1'", and see > if it works. > > Note that you can also use "pg_ctl start -w" to have pg_ctl wait for the > database to fully start up, before it returns. That's one way. Have a look as well at pg_isready: https://www.postgresql.org/docs/9.3/static/app-pg-isready.html it is in 9.3, and is actually a wrapper of PQping(). Once it returns 0 as exit code, it means that the database is ready to accept incoming connections. -- Michael