Questions tagged [read-only-database]
The read-only-database tag has no usage guidance.
65 questions
0
votes
1
answer
39
views
Is it possible to give readonly access to a database during an upgrade
I am upgrading a sql server environment which has an application server, web server and a database server and I want to be be able to give my users readonly access to their data during the upgrade. ...
0
votes
1
answer
53
views
Querying whole PostgreSQL table in replica on CTID
I noticed it is quite fast to query a whole PostgreSQL table in chunks on ctid as explained for example in this Airbyte blog post. I need this for a syncing task that might take from hours to a few ...
- 101
1
vote
2
answers
299
views
How can I monitor query performance on my Azure read-replica database?
I'm looking at Query Performance Insight for my read-replica database. But the information it shows are the performance stats from the primary database. How can I get the performance stats for the ...
- 349
0
votes
1
answer
28
views
Can I create a Geo Replica of a Named Replica?
For clarity, we have two databases on a server in a given region. One database is the primary for the read/write workload, and one is a replica for the read-only workload. I'm trying to set up a ...
- 349
0
votes
2
answers
638
views
What happens to my read-replica database in failover mode?
I have a few basic questions about Azure failover. I am new to failover. I've been reading the literature and I'm ready to give this a shot. Before I take the plunge, I'd like to have a better ...
- 349
0
votes
1
answer
567
views
How do I create user logins on my read replica database?
Is it possible to have different users accessing my read replica DB? Isn't this in fact a popular use case for a read replica? I want a certain limited set of credentials to have access to my primary ...
- 349
0
votes
1
answer
284
views
Cannot do a backup of a read_only database
I'm facing a strange issue where my backup fail with the message :
Msg 3906, Level 16, State 1, Line 5
Failed to update database "MyDBB" because the database is read-only.
Msg 3013, Level ...
- 3,257
-1
votes
2
answers
206
views
readonly user on PostgreSQL 14 let me create new tables, why?
I try to apply any of these to create read_only_user:
https://docs.snaplet.dev/guides/postgresql
https://www.commandprompt.com/education/how-to-create-a-read-only-user-in-postgresql/#:~:text=...
- 101
1
vote
1
answer
687
views
read only replica of azure sql database within the same server
I have the following bicep to create a sql server and sql database:
resource sqlServer 'Microsoft.Sql/servers@2021-02-01-preview' = {
name: sqlServerName
location: location
identity: {
type: ...
- 111
1
vote
1
answer
604
views
Why can't my read-only user role see any of my tables' primary key information in psql?
I have a PostgreSQL database on Azure, and I've created a readonly_user role that allows for external parties to connect to the db in a read-only capacity using a connection string.
This readonly_user ...
- 113
0
votes
1
answer
2k
views
Alter database from Read-Only to Regular mode? A warm-standby database is read-only (Error 5063)
I am trying to change the mode of database from "Standby / Read-Only" to "Normal" mode so that I could use mdf file to attach to other SQL Server.
One instruction that I got was go ...
- 251
4
votes
1
answer
1k
views
Query performance on static, large PostgreSQL table
I tried to have this as detailed as possible. Sorry about the length!
Background
I created the following partitioned table protein_snp_assoc on a PostgreSQL (version 12.13) database:
CREATE TABLE ...
- 43
2
votes
1
answer
918
views
Fix mysql replication crashed due to underlying storage corruption
we have critical database server running on two virtual machines. one virtual machine crashed due to underlying storage problem and we had to move it to different storage medium and recover the xfs ...
- 123
0
votes
1
answer
119
views
Set MySql read-only from K8s cluster
I have a Kubernetes cluster running with MySQL app (5.7.32 MySQL version). I'm trying to make MySQL app be in a consistency mode, using this command:
FLUSH TABLES WITH READ LOCK; SET_GLOBAL read_only =...
- 1
1
vote
1
answer
1k
views
Can we configure logins to only connect to read-only replicas in Azure SQL Database automatically?
For Read-Only routing, rather than explicitly using "ApplicationIntent=ReadOnly" into our application connection string or for individual users to specify it in additional properties, can ...
- 131
0
votes
1
answer
3k
views
PostgreSQL 9.3, set database (or all) to read only mode
Is any simple function/command for PostgreSQL 9.3 to set whole database to read only mode and then back to RW mode?
For example same as for MySQL:
FLUSH TABLES WITH READ LOCK;
LOCK TABLES customers ...
- 191
0
votes
0
answers
268
views
Sql server error message "the database is read only" in a remotely server - availability database
I have a failover cluster with a high availability sql server service, the problem is that I cannot create a table, update, delete remotely even if I already give specific permissions and i had ...
0
votes
0
answers
76
views
Read only database for time series event data
I have a time-series dataset (around 500 million rows, 4 columns) that stores public transport ticket validations.
Not sure if it matters, but it's an event driven dataset, without constant timestamp ...
2
votes
1
answer
1k
views
Sql server/azure connection string parameter for readonly access?
Is there a specific parameter that can be used in a SQL server/azure connection string to specify that the connection should be readonly?
In other words, only SELECT queries should work but INSERT/...
- 546
0
votes
0
answers
4k
views
The MySQL server is running with the --read-only option so it cannot execute this statement
I am using amazon aurora RDS in my application. I have created cluster endpoint by selecting both read and write instance. Then I used that instance in my application. Whenever I am executing update ...
2
votes
2
answers
3k
views
Are Availability Group Databases on a Secondary replica automatically set to read-only by the AG?
I have read through the MS docs on read-only routing, but I am not talking about that.
Currently, if I execute an UPDATE against a database that is on a Secondary replica and is part of the ...
- 1,645
0
votes
1
answer
669
views
Solution for a readonly copy of a standalone MongoDB server
I store some data in many databases/collections on a standalone MongoDB server. This server is used for production.
I need to access those data (or at least nightly build data, and not all databases/...
- 103
0
votes
1
answer
2k
views
Does a read replica help if the problem are heavy queries?
The application that our team is responsible for is having ever more often DB performance problems. The application querying it doesn't throw lots of concurrent queries, the problem we have are rather ...
- 101
0
votes
1
answer
708
views
Does read-only activity on synchronous secondary replica slow down primary replica in Availability Group?
Suppose that we have an Availability Group configuration (SQL Server 2017) where primary and synchronous secondary do not share any resources
(each replica have dedicated CPU, memory, disk, etc.)
...
- 5,964
2
votes
5
answers
4k
views
Prevent read access on primary AG replica, but allow it on the readable secondary
We have a unique situation where we want to allow users to query a readable secondary replica of a database with SSMS for adhoc reporting, but not allow them to ever read from the primary replica. We ...
- 683
0
votes
1
answer
174
views
Configure mongo for one shot full db read
I have a kubernetes cluster on bare metal with 3 machines having 8Gb of ram each. All my apps and a mongodb 4.0.9 replicaset runs on it.
There is an import program that:
Download a 8Gb mongo dump ...
- 281
4
votes
1
answer
1k
views
Query a database specific configuration parameter
I have a postgres 9.6 server with > 100 databases on it. When doing a mass-migrate, I use statements like this:
ALTER DATABASE mydatabase SET default_transaction_read_only = false;
Which works great....
- 223
4
votes
2
answers
26k
views
Insert- and Read-only table in Postgres
I want to create an "immutable" Postgres database, where users can insert/select (write/read) data only, but can not update/delete (change/delete).
I came to know there is FOR UPDATE lock, but did ...
- 155
1
vote
1
answer
321
views
How to set mariadb read_write for some specific tables while starting as read_only=1
I am trying to set a mariadb slave which is exact replica of master db. As per the suggestion I start my slave with command --relay-log=mysql-relay-bin" "--log-slave-updates=1" "--read-only=1" . But ...
- 125
0
votes
1
answer
2k
views
MySQL/MariaDB Read only except for a single table
I Have a database with lots of tables and read_only enabled in the config, is it possible to set a single table to be writable?
2
votes
2
answers
4k
views
Optimising MySQL database with read only tables
I have a MySQL database with 15 tables. Three of them are used for website authentication and three other are tables that are frequently read and written to. Then other tables are set in stone and I'd ...
- 223
7
votes
1
answer
12k
views
Postgres: permission denied for relation for read_only user [closed]
I am trying to set up a new read only user in postgres. I did the following steps to create the new user:
sudo -u postgres createuser --interactive
name: read_only_user
superuser: n
...
- 103
1
vote
1
answer
279
views
Is there any AWS RDS Utility to Manage Master Slave mysql db with read write splitting?
I am looking for a solution for production which can manage the read and write splitting on master and slave connection as i can't affford to define multiple connection string for R/W in whole ...
- 13
2
votes
2
answers
3k
views
Mysql 8.0 and read-only not working as expected
I have a new Master/Slave config running. Everything is happily replication. I have added a secondary user whose role will be to manage the databases and tables.
Currently the following privileges ...
- 21
2
votes
0
answers
1k
views
Secondary Replica Read-Only Routing via Linked Server working from one server, but not the other
I have been given the task of setting up a read-only linked server on ServerA to Availability Group listener ListnerX. ListnerX points to ServerY or ServerZ (ServerY is usually the primary). Read-only ...
- 21
5
votes
2
answers
5k
views
SQL server Read Only permission automatically for new databases
When I setup my database I create a sql user that my application runs as. The sql user gets read only access to all the databases on the server. The problem is the user won’t have read only access to ...
- 153
0
votes
1
answer
811
views
How do I truncate performance_schema tables on a read replica?
On occasion, you have to truncate events_statements_summary_by_digest, when it's hit the row limit. On a write master, this is easy, but on a read replica, there doesn't seem to be a way to do it. The ...
1
vote
2
answers
3k
views
What's the difference between --read-only and just permit SELECT queries?
I'm using Amazon Aurora and I have two endpoints, my endpoint which can read/write are having some issues to execute queries, these are the issues:
SQLSTATE[HY000]: General error: 1290 The MySQL ...
1
vote
0
answers
128
views
How do read queries against Postgres's JSONB type compare to Couchbase?
I'm working on a small project which would involve running queries against deeply-nested (up to 5 layers) keys in dictionaries. This would be specifically a read-heavy environment, with only one large ...
- 23
15
votes
1
answer
469
views
Columnstore Index in read_only filegroup prevents CheckDB
It appears setting a filegroup to read_only prevents dbcc checkdb for the entire database if the filegroup contains a columnstore index. When attempting to run checkdb or checkfilegroup (for any ...
- 5,001
5
votes
1
answer
20k
views
Do I need to execute "create extension pgcrypto" everytime?
I have used crypt() in one of my functions to hash user password. But what I observed the next day I started my pc that the same thing failed as cryptographic functions became unavailable upon restart....
- 153
23
votes
2
answers
37k
views
Make Postgres database temporarily read-only (for performing volume snapshots)
The PostgreSQL built-in backup mechanism isn't always very suitable. Sometimes, you want to put the application in a quiescent state, because it has external data with which you want to backup at the ...
- 654
3
votes
3
answers
4k
views
Unable to create read-only user in PostgreSQL 9.5
I seem to be having trouble creating a read-only user for a database in a fresh install of PostgreSQL 9.5 with PostGIS 2.2 on Ubuntu 16.04. Here is what I've done so far:
Ran this,
CREATE ROLE ...
- 31
3
votes
1
answer
131
views
Does SQL Server route connections to a secondary replica which has commit mode as synchronous in case of an AG with multiple secondary nodes?
If I have an AG with 4 different nodes as below:
Primary Node
Secondary Node 1 - Asynchronous Commit - Read Only Yes
Secondary Node 2 - Synchronous Commit - Read Only No
Secondary Node 3 - ...
- 349
5
votes
2
answers
2k
views
Using postgres_fdw to connect from one read replica to another
I have two postgres database servers in AWS, A and B. These databases have different tables. Both databases also have their own read replica.
My goal is a simple solution for doing cross database ...
- 235
0
votes
1
answer
2k
views
Worry to delete Read replica RDS
I created a read replica from RDS , and I do want to delete it.
I am so worry about affecting the origin RDS which is in PROD .
Is it recommended to take snapshots before deletion of read replica ...
- 101
6
votes
2
answers
32k
views
Obvious reason Postgres Users can't read a table?
Situation: a PG user (non superuser, inherits from parent role) that is a member of a Role/Group cannot read from specific tables even though these Object Privileges have been specified:
DBName - ...
- 211
1
vote
2
answers
165
views
Proc caching on read only db's/replicas
I've been given the task of finding which stored procs are being called in our database. To that end I started querying sys.dm_exec_cached_plans. All is well and good, until I go over to our ...
- 131
6
votes
1
answer
7k
views
Create temp tables in Amazon RDS PostgreSQL read-replica
I have dozens of legacy stored procedures which create temporary tables inside for collecting results for a read-only application.
I've created a read-replica of my PostgreSQL in Amazon RDS and tried ...
- 557
2
votes
3
answers
174
views
Restrictions on user-defined queries
I have a client that wants me to implement a webpage that allows users to enter custom queries in a text box so they can directly query the SQL Server database. I am concerned about users entering ...
- 21