Postgresql Crashed During postgresql-upgrade-database – Database Administrators Stack Exchange

I had Postgresql version 11 on my macOS Catalina. Recently I have upgraded my macOS from Catalina to Big Sur. After upgrading I was trying to start Postgresql with pg_ctl but it was showing version conflict The data directory was initialized by PostgreSQL version 11, which is not compatible with this version 12.4. After searching a few pages I was trying to update my Postgresql by brew postgresql-upgrade-database. In the meantime, my internet was interrupted and the upgrade process stopped during pouring Upgrading postgis 3.0.2 -> 3.0.3. After a long hour, I manually stopped the Upgradation and tried to run again the Upgradation. It was showing a large list of error and warnings which I am giving in the last section of this question

Then I tried to reinstall postgresql@11 and postgresql manually. it was installed but not starting. The pg_ctl version is showing 13.1. brew search postgresql showing postgresql and postgresql@11 is installed. But cannot start anyone of my postgresql server. Now I see in /usr/local/var/postgres directory only one folder remained named postgres.old and when I am trying to start the postgresql@11 or postgresql with pg_ctl -D /usr/local/var/postgres start then it is returning pg_ctl: directory "/usr/local/var/postgres" is not a database cluster directory.

Is there any way to retrieve my data back with a working version of PostgreSQL?

Error and warnings during brew postgresql-upgrade-database

Error: Service `postgresql` is not started.
waiting for server to start....2020-11-25 17:47:07.285 +06 (14418) LOG:  listening on IPv6 address "::1", port 5432
2020-11-25 17:47:07.285 +06 (14418) LOG:  listening on IPv4 address "127.0.0.1", port 5432
2020-11-25 17:47:07.286 +06 (14418) LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2020-11-25 17:47:07.315 +06 (14419) LOG:  database system was shut down at 2020-09-23 02:07:05 +06
2020-11-25 17:47:07.322 +06 (14418) LOG:  database system is ready to accept connections
 done
server started
waiting for server to shut down...2020-11-25 17:47:07.978 +06 (14418) LOG:  received fast shutdown request
.2020-11-25 17:47:07.979 +06 (14418) LOG:  aborting any active transactions
2020-11-25 17:47:07.980 +06 (14418) LOG:  background worker "logical replication launcher" (PID 14425) exited with exit code 1
2020-11-25 17:47:07.980 +06 (14420) LOG:  shutting down
2020-11-25 17:47:07.986 +06 (14418) LOG:  database system is shut down
 done
server stopped
==> Moving postgresql data from /usr/local/var/postgres to /usr/local/var/postgres.old...
==> Creating database...
The files belonging to this database system will be owned by user "azizul.admin".
This user must also own the server process.

The database cluster will be initialized with locales
  COLLATE:  C
  CTYPE:    UTF-8
  MESSAGES: C
  MONETARY: C
  NUMERIC:  C
  TIME:     C
initdb: could not find suitable text search configuration for locale "UTF-8"
The default text search configuration will be set to "simple".

Data page checksums are disabled.

fixing permissions on existing directory /usr/local/var/postgres ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Dhaka
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: 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/local/opt/postgresql/bin/pg_ctl -D /usr/local/var/postgres -l logfile start

==> Migrating and upgrading data...
Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok
Checking database user is the install user                  ok
Checking database connection settings                       ok
Checking for prepared transactions                          ok
Checking for reg* data types in user tables                 ok
Checking for contrib/isn with bigint-passing mismatch       ok
Checking for tables WITH OIDS                               ok
Checking for invalid "sql_identifier" user columns          ok
Creating dump of global objects                             ok
Creating dump of database schemas
  template1                                                 
*failure*

*failure*

Consult the last few lines of "pg_upgrade_dump_683211.log" for
the probable cause of the failure.
Failure, exiting

Consult the last few lines of "pg_upgrade_dump_66436.log" for
the probable cause of the failure.
Failure, exiting

*failure*

Consult the last few lines of "pg_upgrade_dump_642851.log" for
the probable cause of the failure.
Failure, exiting

*failure*

Consult the last few lines of "pg_upgrade_dump_48901.log" for
the probable cause of the failure.
Failure, exiting

*failure*

*failure*

Consult the last few lines of "pg_upgrade_dump_16384.log" for
the probable cause of the failure.
Failure, exiting

Consult the last few lines of "pg_upgrade_dump_18240.log" for
the probable cause of the failure.
Failure, exiting

*failure*

Consult the last few lines of "pg_upgrade_dump_13364.log" for
the probable cause of the failure.
Failure, exiting

child process exited abnormally: status 256
Failure, exiting
Error: Upgrading postgresql data from 11 to 13 failed!
==> Removing empty postgresql initdb database...
==> Moving postgresql data back from /usr/local/var/postgres.old to /usr/local/var/postgre
Could not enable service: 125: Domain does not support specified action
Error: Failure while executing; `/bin/launchctl enable gui/503/homebrew.mxcl.postgresql` exited with 125.
Error: Failure while executing; `/usr/local/opt/postgresql/bin/pg_upgrade -r -b /usr/local/Cellar/postgresql@11/11.10/bin -B /usr/local/opt/postgresql/bin -d /usr/local/var/postgres.old -D /usr/local/var/postgres -j 12` exited with 1.

sql – Dúvida Banco de Dados (Postgresql)

Tabelas Banco de dados

Eu tenho esse exercício: Informe o nome e a data de nascimento dos alunos que estão cursando a disciplina de Banco de
Dados I no primeiro semestre de 2020.

Eu estou tentando fazer ele com subconsulta mas não estou entendendo, alguém saberia? uso o postgresql (sou novo ainda)

postgresql – Optimizing postgres sorting by word_similarity

I have a table with 2M rows of hotel names in Postgresql 12. I am building a typeahead so users can quickly find the hotel by name. I have played around with different Postgres options including FTS, trigrams, and levenshtein distance.

word_similarity in pg_trgm gives me the best results, but whenever I need to sort by the similarity score, things get too slow (without sorting the query finishes in milliseconds):

SELECT name, word_similarity('trade center new york mariott', name) AS sml from hotels_hotel where 'trade center new york mariott' %> name ORDER BY sml DESC LIMIT 5;

                                          name                                          |    sml
----------------------------------------------------------------------------------------+------------
 Courtyard by Marriott New York Downtown Manhattan/World Trade Center Area              | 0.53846157
 Fairfield Inn & Suites by Marriott New York Downtown Manhattan/World Trade Center Area | 0.53846157
 Residence Inn by Marriott New York Downtown Manhattan/World Trade Center Area          | 0.53846157
 AC Hotel by Marriott New York Times Square                                             |    0.53125
 Courtyard by Marriott World Trade Center, Abu Dhabi                                    |  0.5263158
(5 rows)

Time: 12151.654 ms (00:12.152)

EXPLAIN ANALYZE SELECT name, word_similarity('trade center new york mariott', name) AS sml from hotels_hotel where 'trade center new york mariott' %> name ORDER BY sml DESC LIMIT 5;

 Limit  (cost=502890.03..502890.61 rows=5 width=27) (actual time=11119.666..11126.331 rows=5 loops=1)
   ->  Gather Merge  (cost=502890.03..503088.61 rows=1702 width=27) (actual time=11063.630..11070.293 rows=5 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Sort  (cost=501890.01..501892.13 rows=851 width=27) (actual time=11006.845..11006.846 rows=4 loops=3)
               Sort Key: (word_similarity('trade center new york mariott'::text, (name)::text)) DESC
               Sort Method: top-N heapsort  Memory: 25kB
               Worker 0:  Sort Method: top-N heapsort  Memory: 25kB
               Worker 1:  Sort Method: top-N heapsort  Memory: 25kB
               ->  Parallel Seq Scan on hotels_hotel  (cost=0.00..501875.87 rows=851 width=27) (actual time=82.805..10996.658 rows=15070 loops=3)
                     Filter: ('trade center new york mariott'::text %> (name)::text)
                     Rows Removed by Filter: 666002
 Planning Time: 0.921 ms
 JIT:
   Functions: 13
   Options: Inlining true, Optimization true, Expressions true, Deforming true
   Timing: Generation 3.177 ms, Inlining 185.660 ms, Optimization 72.270 ms, Emission 42.028 ms, Total 303.134 ms
 Execution Time: 11128.345 ms
(18 rows)  

Is there any way I can optimize this query? Happy to build a huge index as I suppose is needed.

Why word_similarity is best for my use case:

  • allows typos (notice I misspell marriott in my query)
  • allows different ordering of the words

index – Does PostgreSQL support wildcard indexes on JSON?

I have a multi-tenant service with a table like this:

project_id | user_id | user_properties

Each project belongs to a different customer and customers can freely attach metadata to their users. A project may have millions of users.

Then a customer may want to find some users inside his project filtering with the user_properties (e.g. age grater than X, favorite music equal to Y, etc.)

user_properties can be an arbitrary json of key-value pairs and a customer can run arbitrary queries on the user_properties. The json is not nested (only key-value pairs).

Since a query may return many results it would also be useful to use some sort of pagination (e.g. order by user_id + limit). But pagination, together with arbitrary filters, seems an additional issue for performance…

Is it possible to handle that case in PostgreSQL? Is EAV the only solution?

I see that MongoDB supports wildcard indexes: does PostgreSQL offer anything similar?

postgresql – psql command output to print error via -L output.log

The file specified to -L cannot be made to include errors.

To have errors in the same log file than queries and results, both outputs should be redirected to the same file. For example:

$ cat >script.sql
select 1/0; 

select count(*) from pg_class;

$ psql -e -f script.sql &> output.log

$ cat output.log 
select 1/0;
psql:script.sql:1: ERROR:  division by zero
select count(*) from pg_class;
 count 
-------
   352
(1 row)

See psql --help for more options:

Input and output options:
  -a, --echo-all           echo all input from script
  -b, --echo-errors        echo failed commands
  -e, --echo-queries       echo commands sent to server
  -E, --echo-hidden        display queries that internal commands generate
  -L, --log-file=FILENAME  send session log to file
  -n, --no-readline        disable enhanced command line editing (readline)
  -o, --output=FILENAME    send query results to file (or |pipe)
  -q, --quiet              run quietly (no messages, only query output)
  -s, --single-step        single-step mode (confirm each query)
  -S, --single-line        single-line mode (end of line terminates SQL command)

Optimizing PostgreSQL query with multiple subqueries

I have a rather simple question (I think): I have one query with 2 window functions: COUNT() and AVG(). I also have 10 subqueries, each with a window function (COUNT() or AVG()).

Testing in pgadmin, this query works fast. The problem is: I’m currently testing against only 100 rows.

Here is the query:

SELECT
    COUNT(a.review_id)::int AS total_reviews,
    COALESCE(AVG(a.score), 0)::int AS average_score,
    (SElECT COUNT(review_id) FROM data.users_pro_reviews WHERE user_id = $1 AND score = 1)::int AS total_1,
    (SElECT COUNT(review_id) FROM data.users_pro_reviews WHERE user_id = $1 AND score = 2)::int AS total_2,
    (SElECT COUNT(review_id) FROM data.users_pro_reviews WHERE user_id = $1 AND score = 3)::int AS total_3,
    (SElECT COUNT(review_id) FROM data.users_pro_reviews WHERE user_id = $1 AND score = 4)::int AS total_4,
    (SElECT COUNT(review_id) FROM data.users_pro_reviews WHERE user_id = $1 AND score = 5)::int AS total_5,
    COALESCE((SELECT AVG(score) FROM data.users_pro_reviews WHERE user_id = $1 AND timestamp_reviewed < NOW()), 0)::int as today,
    COALESCE((SELECT AVG(score) FROM data.users_pro_reviews WHERE user_id = $1 AND timestamp_reviewed < NOW() - INTERVAL '1 DAY'), 0)::int as yesterday,
    COALESCE((SELECT AVG(score) FROM data.users_pro_reviews WHERE user_id = $1 AND timestamp_reviewed < NOW() - INTERVAL '1 WEEK'), 0)::int as one_week_ago,
    COALESCE((SELECT AVG(score) FROM data.users_pro_reviews WHERE user_id = $1 AND timestamp_reviewed < NOW() - INTERVAL '1 MONTH'), 0)::int as one_month_ago,
    COALESCE((SELECT AVG(score) FROM data.users_pro_reviews WHERE user_id = $1 AND timestamp_reviewed < NOW() - INTERVAL '6 MONTH'), 0)::int as six_montsh_ago
FROM
    data.users_pro_reviews a
WHERE
    a.user_id = $1

How can this query be optimized? Using JOINS? And how should I use window functions in JOINs?

servidor PostgreSQL – Stack Overflow en español

debo crear una base de datos con PostgreSQL pero necesito que 2 personas mas entren a mi base de datos ¿es posible?.
Lo he intentado con wamp he estado buscando y configurando y junto con PhpPgAdmin al momento de iniciar sesión me devuelve al inicio, ya se ha configurado dentro de todo “$conf[‘extra_login_security’] = false;” y se han abierto los puertos correspondientes.

Programas Ocupados:

  • PostgreSQL
  • PhpPgAdmin
  • Wampserver

PostgreSQL database with 9000 tables continuously grow in memory usage

I have a PostgreSQL database that I use to store time-series (finance) data. each table contains the same table schema but has a different name based on the market pair and timeframe.

Ex. I have tables called candles_btc_usdt_one_minute, candles_eth_usdt_one_hour, candles_eth_usdt_one_week, etc.

These tables sum up to around 9000 tables in total.

Note that I know about TimescaleDB and InfluxDB, I already tested both and will post a reason I’m not using them at the end of this post.

So, since this is time-series data, it means that I’m only doing INSERT write operations and very rarely some SELECT to retrieve some data.

My issue is that the database memory usage seems to grow infinitely until I get an OOM crash. I configured my postgresql.conf using solutions as PGTune to a system with 1GB of RAM, 6 cores, and 120 connections and I limited my docker container to 4GB and still got an OOM after around one day with the system on.

I also tried other configs as 4GB of ram and 8GB in the container but PostgreSQL never respects the limit stipulated by the config and keeps using more and more RAM.

Is this the expected behavior? Maybe PostgreSQL has some other obscure config I can use to limit the memory usage in cases where there is a huge number of tables.. I’m not sure..

The reason I’m guessing this issue has something to do with my high number of tables is because the opened connections from my connection pool keep growing in memory usage faster at the start of my system (the first hours) and then the growth gets slower (but never stops).

That behavior reflects my INSERT intervals when hitting the tables.

For example, a table with a timeframe five_minutes means that every five minutes I will insert a new row to it, which means that I’m accessing these tables for the first time faster when the system starts than tables with higher timeframes as one_hour, etc.

And monitoring the memory growth, it seems that the connection process grows a little bit when it accesses a new table for the first time.

So, assuming this is right, it would mean that after some months, all the connections would have accessed all the tables at least one time and memory growth would stop. The problem with that is that I don’t know how much memory this would use at the end and it’s not ideal since trying to limit the memory via postgresql.conf becomes meaningless.

Here is the schema for one of the tables (as I said before, all tables has the same columns, index, etc):

data_db_prod=# d+ candles_one_minute_btc_usdt
                                   Table "public.candles_one_minute_btc_usdt"
  Column   |            Type             | Collation | Nullable | Default | Storage | Stats target | Description 
-----------+-----------------------------+-----------+----------+---------+---------+--------------+-------------
 timestamp | timestamp without time zone |           | not null |         | plain   |              | 
 open      | numeric                     |           | not null |         | main    |              | 
 close     | numeric                     |           | not null |         | main    |              | 
 high      | numeric                     |           | not null |         | main    |              | 
 low       | numeric                     |           | not null |         | main    |              | 
 volume    | numeric                     |           | not null |         | main    |              | 
Indexes:
    "candles_one_minute_btc_usdt_timestamp_desc_index" btree ("timestamp" DESC)
    "candles_one_minute_btc_usdt_timestamp_index" btree ("timestamp")
Access method: heap

About other solutions

As I said before, I already tried TimescaleDB and InfluxDB.

For TimescaleDB I would be able to use a single candles table and create 2 partitions to store the market pair and the timeframe, fixing the high number of tables and probably the RAM issue I’m having, but I cannot use this because TimescaleDB uses too much storage, so I would need to use their compression feature, but a compressed hypertable doesn’t allow write operations, meaning that to be able to do a backfill (which I do often) I would need to basically decompress the whole database each time.

For InfluxDB the issue is simply because they don’t support any numeric/decimal type, and I cannot lose precision using double.

Feel free to suggest some other alternative I’m not aware of that would fit nicely into my use case if there is one.

postgresql – Is it safe to pg_dump and pg_restore a new postgres database that has malware?

I’m pretty sure there is a crypto bot eating up my CPU through a postgres script. I would like to create an entirely new VM, and move my database with it using pg_dump and pg_restore. I already checked my postgres for new users, tables, databases; couldn’t find anything odd there which could comprise me if I move my data. I’m a little worried however because the bot is some how getting access to my postgres, and nothing else on my VM.

Thank you for the help.

passing variable values to procedure in postgresql via psql

basically I have written a code to pass variable values via psql to my procedure is it supported?
and if supported any pointers to get it done : my code looks like below :

do $$
<<main_block>>
declare
err_msg text;
BEGIN
insert into mig.qb_items (qb_id , sync_token , qb_updated_at , qb_created_at , qb_expired_at , name , sku , description , active , qb_parent_id , taxable , unit_price , transaction_type , category_type , qb_income_account_id , qb_expense_account_id , purchase_description , tax_included , purchase_cost , qb_asset_account_id , track_quantity , inventory_start_date , quantity_on_hand , qb_sales_tax_id , qb_tax_code_id , abatement_rate , reverse_charge_rate , service_type , level, created_at , updated_at , expired_at , ancestor_id , signature , financial_signature , qb_object , print_grouped_items, company_id )
select qb_id , sync_token , qb_updated_at , qb_created_at , qb_expired_at , name , sku , description , active , qb_parent_id , taxable , unit_price , transaction_type , category_type , qb_income_account_id , qb_expense_account_id , purchase_description , tax_included , purchase_cost , qb_asset_account_id , track_quantity , inventory_start_date , quantity_on_hand , qb_sales_tax_id , qb_tax_code_id , abatement_rate , reverse_charge_rate , service_type , level , created_at , updated_at , expired_at , ancestor_id , signature , financial_signature , qb_object , print_grouped_items, :tenant_id as company_id from :schema.qb_items;
update mig.migration_status set status=’COMPLETED’ WHERE schema=:tenant and table_name=’qb_items’;
EXCEPTION WHEN others THEN
GET STACKED DIAGNOSTICS err_msg = MESSAGE_TEXT;
update mig.migration_status set status=’FAILED’ WHERE schema=:tenant and table_name=’qb_items’;
raise notice ‘EXCEPTION %’, err_msg;
END main_block;
$$;

bt i am getting error as below :

psql:/home/ec2-user/scripts/qb_items.sql:14: ERROR: syntax error at or near “:”
LINE 7: …cial_signature , qb_object , print_grouped_items, :tenant_id…