Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [read-only-database]

55 questions
19
votes
2 answers

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 same time you back up the PG data. But the only…
Otheus
  • 554
  • 1
  • 3
  • 13
15
votes
1 answer

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 filegroup in the database, including read-write…
Peter Vandivier
  • 4,378
  • 1
  • 22
  • 42
7
votes
1 answer

Postgres: permission denied for relation for read_only user

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 create database: n Create new roles: n Then I…
mgerbracht
  • 93
  • 1
  • 2
  • 4
5
votes
1 answer

Revoke write privileges vs setting database to readonly

I am planning to do a database migration to a new server. During the transition stage, I do not want Apache user to be able to write anything to the database. I have two options. Revoke write privileges: REVOKE INSERT, UPDATE ON `mydb`.* FROM…
Question Overflow
  • 959
  • 6
  • 15
  • 21
5
votes
2 answers

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 any new created databases in the future. There…
Jason Webber
  • 153
  • 1
  • 5
5
votes
2 answers

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 joins from A replica to B replica. Querying from…
maniciam
  • 235
  • 1
  • 6
5
votes
2 answers

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 - Connect SELECT - true INSERT - true Delete -…
GPP
  • 201
  • 1
  • 2
  • 6
5
votes
1 answer

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 to perform this procedures, but failed, as it…
Andremoniy
  • 527
  • 1
  • 6
  • 16
4
votes
1 answer

Create new Read Only user on DB2 on the whole database

I am an oracle/sql server DBA having limited knowledge in IBM DB2. We have DB2 9.7 fp4 running on AIX 6. I would like to create a new user and grant him READ only privilege on a particular database. Please help! Thanks & Regards Mohammed
muddu83
  • 153
  • 1
  • 1
  • 4
4
votes
1 answer

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. It showed message like functions gen_salt() /…
Arjun_TECH
  • 143
  • 1
  • 1
  • 6
3
votes
0 answers

Table queries locked during long insert SQL server

Everyday runs a sql job that loads data from bulk files and load them in auxiliary tables, to adapt and then copy to the destination table where applications read (and only read) the data. I am focusing on a table 'Accounts' that is accessed…
Rodrigo Rivera
  • 31
  • 2
3
votes
0 answers

Optimizing a single table, write-once, read-many PostgreSQL database

I've just finished constructing a table of ~835 million rows using Google's ngram dataset, aggregated on the years in which they occurred so that each 2-,3-,4-, and 5-gram is represented by a single row in the table. Each word in each ngram also has…
James Pizzurro
  • 31
  • 2
3
votes
1 answer

How to start mysqld with read-only data directory?

I'm trying to start MariaDB in a docker container, using a data directory from the host mounted read-only. MySQLd is of course shut down on the host. Unfortunately, even with adding the read_only flag to my.conf, I get the following…
Thomas Johnson
  • 461
  • 2
  • 5
  • 12
3
votes
2 answers

How do I map a SQL Server login to a user that already exists in a read-only database?

One of our database users complains that they can't select from one of the databases in this particualr instance while they have no problems in accessing others in this very same instance. The one that they have access problems is shown as "Standby…
RGO
  • 413
  • 4
  • 12
3
votes
1 answer

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. However, occasionally I get into a state where…
spookypeanut
  • 173
  • 6
1
2 3 4