43
votes
Why do I get a PostgreSQL permission error when specifying a tablespace in the "create database" command?
Here's the trick, at least with pgAdmin v4:
Leave the tablespace blank. It will default to "pg_default" when creating the database.
- 531
41
votes
CPU usage constantly high after upgrading from Postgres 9.5 to 9.6 to 10.6
ANALYZE VERBOSE;
You need to run an ANALYZE query on the whole database using the above command.
The issue is that the query plans postgres has generated are optimised for the previous version of ...
- 511
21
votes
Best way to pg_dump PostgreSQL on RDS to S3
You can directly upload the (gzipped) pg_dump results to s3 using the aws cli:
pg_dump -Z 9 -v -h ${DATABASE_HOST} -U ${DATABASE_USER} -d ${DATABASE_NAME} | aws s3 cp --storage-class STANDARD_IA --...
- 361
19
votes
Accepted
What does KEY SHARE OF in a PostgreSQL statement mean?
This is the effect of inserting or updating a row in a table that has a foreign key. See the following example:
CREATE TABLE a (id integer PRIMARY KEY);
CREATE TABLE b (id integer REFERENCES a);
...
- 48k
16
votes
MS SQL Express 2016 on Amazon AWS: I Can Take Database Offline but Can't Bring It Online
You are correct: your admin account is not sysadmin, but a less powerful account. This is necessary to ensure that AWS is able to limit you from doing EVERYTHING to this instance, and escaping ...
- 16.1k
14
votes
Does amazon rds PostgreSQL require vacuum?
The RDS Documentation states:
Autovacuum is enabled by default for all new Amazon RDS PostgreSQL DB instances, and the related autovacuum configuration parameters are appropriately set by default. ...
- 241
14
votes
Accepted
Upgrade all MySQL columns, tables, and databases from utf8mb3 to utf8mb4
Here is a query what will generate all the necessary update statements.
It temporarily disables foreign key checks so that the update statements succeed.
It updates the default character set for each ...
13
votes
Does AWS RDS support MEMORY storage engine?
This answer applies to AWS Aurora not straight MySQL RDS.
Just because SHOW ENGINES says the database has the MEMORY storage engine doesn't mean you can actually use it. We have found that in AWS ...
- 131
12
votes
How to grant set the replication attribute on RDS PostgreSQL?
We hit this head on too, and after much frustration discovered:
CREATE ROLE some-user WITH LOGIN PASSWORD 'long-password';
grant rds_replication to user;
This seems to be working for logical ...
- 503
12
votes
How to alter system on AWS RDS?
The RDS Postgres instance has a parameter group. Check in RDS colsole what parameter group is defined: RDS -> Databases -> choose your db -> Configuration -> parameter group. If it's the default one, ...
- 386
12
votes
Accepted
Adding a Primary Key to a Large PostgreSQL Table with High Traffic
Why?
Your step 1 would need a lot more than 600 GB (temporarily). The table has around 2 TB. About as much (minus possible bloat, plus 8 bytes per row for the new bigint column) has to be available at ...
- 172k
11
votes
ERROR: no schema has been selected to create in
I've tried multiple solutions, and keep getting the same error over and over.
Worse to mention, the error appears to me when I needed to restart migration of my DB by deleting public schema and after ...
- 211
11
votes
Accepted
Improve UPDATE performance on big table
Assuming the column uuid is supposed to be UNIQUE, this table definition should save some space and help performance:
CREATE TABLE public.calls (
id serial PRIMARY KEY,
result int4 DEFAULT 0 ...
- 172k
10
votes
ERROR: no schema has been selected to create in
I had pgdump file with creating functions in custom schema and I wanted to switch custom schema name to general public and replaced all occurences with old schema to empty (e.g. myschema.tablename to ...
- 201
10
votes
Accepted
How do I configure AWS Aurora to separate write/read operations
AFAIK, you're right that AWS RDS Aurora (a MySQL 5.6 fork) does not support automatic or transparent read/write splitting:
http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_Aurora.html
In ...
- 1,281
10
votes
Accepted
AWS DMS creates tables with no auto_increment
If DMS is told to create the table on the destination (the default option) it only creates the most basic structure needed to move the raw data over. So you shouldn't trust that other metadata like ...
- 308
10
votes
Accepted
MySQL commits taking time
Since your commits are stalling, it's pretty safe to assume you're running with innodb_flush_log_at_trx_commit=1 (see #4 here), so one obvious change you could do is switch to ...
9
votes
How do I configure AWS Aurora to separate write/read operations
I just wanted to point out that AWS has updated and now have a cluster Read endpoint that does load balancing in case anyone runs into this from Google.
https://aws.amazon.com/blogs/aws/new-reader-...
- 99
9
votes
Accepted
Achieving "sequence number", rather than dateTime, for updates; postgres specifically
As a_horse_with_no_name originally suggested in question comments:
--assuming we have tables: a, b, ...
CREATE TABLE a
(
a_id integer NOT NULL PRIMARY KEY,
a_name text
) ;
CREATE TABLE b
(
...
- 97.4k
9
votes
Why am I getting permission denied on an ownership change?
Thanks to @Spike for comment, for solution which worked for me (source)
This assumes that objects are reassigned from role admin to role new_admin.
1.Create a new role:
mydb=> CREATE ROLE ...
- 495
8
votes
Accepted
Debug query on big table that is sometimes slow
Table definition
After trimming the noise:
CREATE TABLE public.details (
value numeric,
created_at timestamptz NOT NULL,
updated_at timestamptz NOT NULL,
effective_date timestamptz,
...
- 172k
8
votes
Diagnosing high write throughput on RDS Postgres
It turns out that the vast majority of my write throughput was coming from the stats collector. Because of the very high number of relations in my database, the stats data is unusually large. I was ...
- 271
8
votes
Accepted
pg_dump permission denied for sequence
Might be an AWS-RDS specific issue but it works if you explicitly specify the schema to be dumped.
pg_dump -f lol.sql -Fc -v -n public -O -x --section=post-data -h 127.0.0.1 -p 9999 -U root db-name
- 196
8
votes
Accepted
Postgres 9.4 wal_level=logical increases disk space
This is an AWS DMS issue.
DMS has recently added a feature WAL heartbeat [1] (run dummy queries) for replication from a PostgreSQL source so idle logical replication slots do not hold on to old WAL ...
- 271
7
votes
Accepted
AWS RDS - Changing Instance Class
Changing Instance Class:
From the documentation "Modifying a DB Instance and Using the Apply Immediately Parameter", changing the instance class and setting "Apply Immediately" to false will result ...
- 333
7
votes
#1041 - Out of memory issue in mysql
PROBLEM #1
Your major problem is having at least 10000 tables
Here is the issue I see
RDS Instance was All InnoDB
How many file handles are open if you have 10000 InnoDB tables ?
One file handle for ...
- 182k
7
votes
Accepted
"Cannot resolve table name" error when applying MySQL dump to RDS
Just found out that this was a bug that has been fixed in the next release, MySQL 5.7.11.
Extract from the changelog:
InnoDB: Creating a table with a full-text index and a foreign key constraint ...
- 698
7
votes
Accepted
Amazon RDS remove read-only and allow defining table structures
In my case, I ran into this problem on one of my stacks—but not the first time the stack was built. It seems I was referencing an AWS::RDS::DBInstance's DBInstance.Endpoint.Address, but that instance ...
- 186
Only top scored, non community-wiki answers of a minimum length are eligible