All Questions
99,821
questions
3
votes
2
answers
410
views
Getting Postgres CREATE TABLE statements
I created some tables (9, to be exact) with a Django (1.9.6) migration and now I'm trying to get simple CREATE TABLE statements for them. I tried this answer, but using pg_dump in this way gives me ...
CommunityBot
- 1
1
vote
1
answer
575
views
Parallel insert and delete into staging tables
I work on a small system that receives quite a lot of data from multiple sources (hundreds of them). Depending on few factors I can get anywhere from few rows to a couple of thousands (in fact ...
CommunityBot
- 1
0
votes
1
answer
455
views
Repmgr : It automatically promotes to new master but other standby stopped
I'm trying to setup postgre with repmgr and here is the rsult
The scenario is,
i tried to spinup 1 primary and 2 standby.
then i stop the primary so the postgres-2 got promoted.
Unfortunately the ...
CommunityBot
- 1
0
votes
0
answers
7
views
Configuring a DNN listener for an availability group fails
I am running into problem when creating DNN listener on Azure VM.
I am runnign the script provided in Microsoft article:
https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/...
- 55
1
vote
2
answers
565
views
SqlPackage importing of very large bacpacs to Azure
I want to import a database from a .bacpac file to a SQL Server in Azure. I read the document.
It says that there is a flag called DatabaseMaximumSize=(INT32).
I wanted to know if there's a limit that ...
CommunityBot
- 1
0
votes
1
answer
58
views
Why is the SYS user granted both the DBA role and the SYSDBA privilege?
If the SYSDBA privilege gives the grantee ultimate authority, then why does the SYS user also have the DBA role in addition to the SYSDBA privilege?
- 123
0
votes
1
answer
406
views
Index maintenance on pg_catalog tables in AWS RDS
I have had some bad historical usage on my Aurora PostgreSQL instance. Namely, connections that create a lot of temporary objects and some very long running transactions (which limits autovacuum).
Now ...
CommunityBot
- 1
0
votes
0
answers
13
views
AWS RDS Postgres: How to see which query/process is using memory
Running a RDS Aurora Postgres cluster with two r6g.2xlarge instances that started fluctuating in memory usage around 6:50pm EST yesterday. Typically we are running with ~16gb headroom but it dips to ~...
- 101
2
votes
1
answer
3k
views
Access denied error in trying to connect to a MySQL db in Python
I am trying to write a script that connects to a server, then connects to a MySQL db (which I currently can do via Navicat - so I know my username and password for the MySQL connection are correct).
...
CommunityBot
- 1
-2
votes
1
answer
28
views
Is there any database uses lzma?
according to this article, lzma has a great performance on compression ratio. I think lzma database is a good choice if I don't care about compression speed, so is there any database that uses it?
- 594
0
votes
0
answers
22
views
postgresql with timescaledb backup/restore and upgrade issue
My customer is using TimescaleDB 2.3.1 at PostgreSQL 11.20. We are trying to upgrade PostgreSQL and Timescaledb currently
I installed PostgreSQL 13.11 and and TimescaleDB 2.11.0
I backedup the ...
- 15
1
vote
1
answer
1k
views
How are the DBA, SYSDBA, SYSOPER roles and SYS, SYSTEM users related in Oracle?
I don't know if I correctly understand the privileges of these built-in roles and users, but this is a list of the information that I have found:
The DBA and SYSDBA roles enable you to do everything. ...
- 123
0
votes
1
answer
1k
views
Multiple files found for the same tablespace
how to fix this error?
2021-11-22T19:40:23.065929Z 0 [System] [MY-010116] [Server] c:\openserver\modules\database\MySQL-8.0-Win10\bin\mysqld.exe (mysqld 8.0.24) starting as process 4892
2021-11-22T19:...
CommunityBot
- 1
2
votes
1
answer
39
views
psql \set an environment variable from the existing shell environment
I want to set up a psql variable from an existing shell environment variable that I can use in later psql commands.
I found the \set meta-command, but I'm not able to make it use an existing ...
- 29.8k
0
votes
0
answers
47
views
Why Postgres on GCP is not using all the available memory?
I have a Postgres instance on GCP with incredibly slow queries. The database structure is very bad, but I believe something else is also affecting performance.
Although the instance has 4GB of memory ...
gcstr
2
votes
1
answer
2k
views
mysql cluster table full error
I installed a new mysql cluster on windows (for evaluation). I created a table using command below. After I insert just over 1 million records into the table it gives me the "table datadump is full" ...
CommunityBot
- 1
0
votes
1
answer
750
views
pt-online-schema-change error creating triggers
I am trying to add an index with pt-online-schema-change. The dry-run succeeds, but I am stuck at creating triggers. Any help ?
pt-online-schema-change --alter "ADD INDEX domain_index (domain) USING ...
CommunityBot
- 1
22
votes
5
answers
23k
views
mysql to mariadb: unknown collation utf8mb4_0900_ai_ci
I have a mysql 8.0 that I exported using mysqldump. I am trying to import it onto a Mariadb 10.4 database with phpmyadmin, both are the most current versions. Each time I do it though, I get:
Error: ...
- 101
7
votes
3
answers
19k
views
GROUP BY gives wrong result with MIN() aggregate function
I have a table that looks like this:
+------+--------+----------+------+--------+-------+------+------------+
| id | code | category | mq | weight | weave | show | min(price) |
+------+--------+...
CommunityBot
- 1
1
vote
2
answers
35
views
Best way to store millions of measurements with hundreds of columns
I work with infrared spectrometers (several hundred of them) to analyze chemical compounds, and I am looking for an efficient solution to store the measured data so that I can process it later on.
...
- 24.4k
0
votes
2
answers
1k
views
mysql - best way to provide unique voucher
I am designing a website that will provide vouchers (serial codes). All voucher codes generated and saved in a table like this and not generated online:
v_id voucher ...
CommunityBot
- 1
-3
votes
0
answers
38
views
how do you improve a search on a column which is not a primary key eg searching by name on a very large table to get an account number
I am using an Oracle database 19c. I have a table for all the account numbers of our customers. Searching by account number is very fast but they want to search for the account number using the ...
- 24.4k
0
votes
0
answers
20
views
Is there any way to exchange the name of a table with a current table online?
A table currently syncing using ogg on target side has a few differences with the source side. So I will resync the table to a new name on target side alone. But how can I exchange the table name with ...
- 24.4k
0
votes
2
answers
4k
views
output psql results and errors to a file
I want to display the results of psql queries on a file.In the log just the name DO for the anonymous Bloc is being displayed.
The following is being executed from a shell script file :
psql --host=...
1
vote
1
answer
415
views
SAP BO Freehand SQL date formatting (Oracle db)
My original query worked perfectly via SQL Developer:
select * from [table] where DT_CURRENT_DT = '15-OCT-18'
However when ran via freehand SQL in SAP BO I got no results.
After running the query ...
CommunityBot
- 1
0
votes
0
answers
596
views
SSRS 2016 issue - Web Portal URL is showing "Could not load folder contents "
I need help for one if report server issue, We have migrated the SQL server from 2014 to 2016 using in-place upgrade. After upgradation we are having issue with web portal URL and it is showing "Could ...
- 1
0
votes
0
answers
15
views
Escape single quotes when passing command to psql -c
I need to run the following command in a BASH shell:
sudo -u postgres /usr/pgsql-15/bin/psql -c "select count(*) from web_site where web_page like '%something%' " -d 'some_database'
The ...
1
vote
0
answers
43
views
Super slow index scan from IN subquery (generated by third party software). Tuning postgres or rewriting query?
I have a query which has a huge bottleneck. Doing explain analyze I found out that an index scan is causing the problem.
The query is:
select * from product where (product.id = IN(select pu.id from ...
- 11
0
votes
1
answer
470
views
Filling in Dates - Postgres/Redshift SQL
I have a dates table with a "calendar_date" field and I have been trying to utilize it for when I need to fill in missing dates when an id does not have a record for a particular day AND ...
CommunityBot
- 1
4
votes
1
answer
128
views
SQL Server linked server's DATA ACCESS property changes to false
I have a SQL Server instance that includes an agent job that accesses a second instance via a linked server. Occasionally, and seemingly randomly, the DATA ACCESS property of the linked server ...
- 3,500
3
votes
1
answer
1k
views
speed up PostgreSQL calculations via OLAP warehouse?
So I have asked these three questions:
Percentile rank that takes sorted argument (or same functionality) in PostgreSQL 9.3.5
https://quant.stackexchange.com/questions/29572/building-financial-data-...
CommunityBot
- 1
5
votes
6
answers
10k
views
Why does SELECTing sum() return null instead of 0 when there are no matching records?
This is but one of countless little details that make me frustrated all the time, forces me to memorize all kinds of special code and causes my queries to become uglier than they should have to be.
...
0
votes
1
answer
1k
views
Large long-running reporting queries are causing very slow new mysql connections
Usually, mysql's connecton time is in the 0.001 second range. However, when I run a large reporting query (that spans multiple tables with aggregates), I get tons of warnings saying that new mysql ...
CommunityBot
- 1
-1
votes
1
answer
32
views
How to load data into a table from a file and set other column(s) too?
I have a table with multiple columns. And there's also a text file which contains data, one value per line:
aaa
bbb
ccc
ddd
How would I load a file into a file into a certain column as well as set ...
0
votes
1
answer
12
views
Does the order in which vms are shutdown impact the postgresql replication environment?
I have the following environment:
A postgres server (rhel8, postgresql v14) as master and another as standby.
I need to do a maintenance task on the infrastructure and for this reason both vms need to ...
- 42.5k
1
vote
1
answer
797
views
SQL Get Users By One Group But Exclude Others
I'm trying to write a query that get's all users that belong to Group A, but excludes users that belong Groups B, C & D.
Users can belong to multiple groups.
I have the following query but it's ...
CommunityBot
- 1
0
votes
1
answer
14
views
PostgreSQL-15, how to retrieve table owner by given database name and table name?
I have multiple PostgreSQL-15 databases with different names to fulfil our business operational data, such as: pilot, production and archive data in different purpose.
I am trying to write a shell ...
- 42.5k
0
votes
1
answer
247
views
pgAdmin 4 v 7.1 not loading after trying everything
I am having serious problems getting pgAdmin 4 to load. I'm running on Windows 10. Please see attached screenshot. I have naturally tried to troubleshoot this problem myself. I have followed ...
33
votes
2
answers
73k
views
Improve performance of COUNT/GROUP-BY in large PostgresSQL table?
I am running PostgresSQL 9.2 and have a 12 column relation with about 6,700,000 rows. It contains nodes in a 3D space, each one referencing a user (who created it). To query which user has created how ...
- 167k
0
votes
1
answer
602
views
Sqlite performance issues due to large number of columns
Backstory:
I'm working on a project that maps a large set of data across geographic locations and time (this is a personal project so I have complete control in terms of optimizations)
Writes are ...
CommunityBot
- 1
0
votes
1
answer
335
views
mysql 8 ndbcluster distributed privileges
I've setup a mysql 8 ndbcluster but cannot get the distributed privileges working.
following how to setup distributed privileges using NDB_STORED_USER (https://dev.mysql.com/doc/refman/8.0/en/mysql-...
CommunityBot
- 1
0
votes
0
answers
7
views
Postgres 14 upsert column where nulls are unique
I have a table with a column where only 1 null is allowed (nulls should be considered unique).
I can a create partial index on this column to enforce this rule.
However I can't find a way to use that ...
- 101
-1
votes
0
answers
33
views
XAMPP Control Panel mysql shutdown unexpectedly
When I try to start on mysql from xampp control panel it says:
20:52:08 [mysql] Status change detected: stopped
20:52:08 [mysql] Error: MySQL shutdown unexpectedly.
20:52:08 [mysql] This may ...
- 7,223
0
votes
1
answer
594
views
Mongodb index keeps growing in size eventhough the collection has a TTL index
I work on a mongodb sharded cluster (3.0.2 i know it's very outdated, not my call), on which i have a sharded db and a sharded collection on which i have multiple indexes.
One of the indexes is a TTL ...
CommunityBot
- 1
-1
votes
0
answers
29
views
Loading 90 million records into a MySQL table
I am trying to load 90 million records into a mysql table and I am having tons of trouble. First I tried loading it from phpmyadmin (using LOAD DATA INFILE...) but that did not work. Then I tried with ...
jack.linton
4
votes
2
answers
3k
views
Way to obtain full list of column dependencies for stored procedure?
I've built a stored procedure (which we can call sproc_deps) that uses sys.sql_expression_dependencies and sys.dm_sql_referenced_entities. I want it to list out all of the tables and columns used by a ...
CommunityBot
- 1
1
vote
2
answers
388
views
Optimizing table for timeseries Postgres data table
I have the below table which maintains a timeseries result. The row only becomes relevant when the signal is true, When signal is false, it just marks that for that particular timestamp we got a ...
CommunityBot
- 1
-3
votes
1
answer
25
views
SQL Server log shipping for DC and DR on cloud
If I would like to do log shipping to cloud DC DR.
But the DR server at normal times will be turned off.
It will turn on only if the DC has failed.
So is it possible way to do it? when the DC failed ...
- 24.3k
0
votes
1
answer
40
views
postgreSQL update queries running longer
I have a query where processing status code for staging records are updated at the end of the processing, we have created daily partition on this table based on row_created_timestamp. On an average, ...
1
vote
3
answers
563
views
TDE using EKM device
We use an external HSM device to create and store keys and use it for TDE.
Our auditors are asking questions about where the keys are store. So obviously the key is stored in the external HSM device. ...
