You can use SHOW: SHOW max_connections; This returns the currently effective setting. Be aware that it can differ from the setting in postgresql.conf as there are a multiple ways to set run-time parameters in PostgreSQL. To reset the “original” setting from postgresql.conf in your current session: RESET max_connections; However, not applicable to this particular setting. … Read more
With Postgres 9.6 this can be done using the option if not exists ALTER TABLE table_name ADD COLUMN IF NOT EXISTS column_name INTEGER;
Because the number can be up to 15 digits, you’ll need to cast to an 64 bit (8-byte) integer. Try this: SELECT * FROM table WHERE myint = mytext::int8 The :: cast operator is historical but convenient. Postgres also conforms to the SQL standard syntax myint = cast ( mytext as int8) If you have … Read more
There is no implicit (automatic) cast from text or varchar to integer (i.e. you cannot pass a varchar to a function expecting integer or assign a varchar field to an integer one), so you must specify an explicit cast using ALTER TABLE … ALTER COLUMN … TYPE … USING: ALTER TABLE the_table ALTER COLUMN col_name … Read more
No need to touch hot_standby_feedback. As others have mentioned, setting it to on can bloat master. Imagine opening transaction on a slave and not closing it. Instead, set max_standby_archive_delay and max_standby_streaming_delay to some sane value: # /etc/postgresql/10/main/postgresql.conf on a slave max_standby_archive_delay = 900s max_standby_streaming_delay = 900s This way queries on slaves with a duration less … Read more
find the file pg_hba.conf – it may be located, for example in /etc/postgresql-9.1/pg_hba.conf. cd /etc/postgresql-9.1/ Back it up cp pg_hba.conf pg_hba.conf-backup place the following line (as either the first un-commented line, or as the only one): For all occurrence of below (local and host) , except replication section if you don’t have any it has … Read more
[*] Yes, this is a common aggregation problem. Before SQL3 (1999), the selected fields must appear in the GROUP BY clause[*]. To workaround this issue, you must calculate the aggregate in a sub-query and then join it with itself to get the additional columns you’d need to show: SELECT m.cname, m.wmname, t.mx FROM ( SELECT … Read more
Type \q and then press ENTER to quit psql. UPDATE: 19-OCT-2018 As of PostgreSQL 11, the keywords “quit” and “exit” in the PostgreSQL command-line interface have been included to help make it easier to leave the command-line tool.