Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [schema-migration]

24 questions
17
votes
2 answers

Best practices for keeping protobuf spec in sync with relational database (e.g. PostgreSQL)

1. We have a schema definition in Google's Protocol Buffers format that we use to generate code for different gRPC implementations (Python, PHP) 2. We have a database (PostreSQL) that is initialized from SQL files which map the Protocol Buffers…
Alex
  • 413
  • 1
  • 5
  • 12
5
votes
2 answers

Can I change the owner of the cdc schema?

I've enabled Change Data Capture (CDC) on a database using exec sys.sp_cdc_enable_db This creates a new cdc schema which is owned by the cdc user select s.* , dp.name from sys.schemas as s join sys.database_principals as dp on…
Zikato
  • 5,619
  • 1
  • 17
  • 34
5
votes
3 answers

Migrating existing tables to use INT primary key instead of UUID mysql v8

I'd love to hear everyone's opinion on this issue. I currently use Innodb mysql v8 in RDS. Our db is moderate, with ~130 tables, most are small, with our largest table having ~30 million rows over 6 years. We've currently scaled our RDS instant…
Muab Nhoj
  • 427
  • 2
  • 8
2
votes
2 answers

Transferring schema from MySql to MariaDB

My development environment has XAMPP, which means I have MariaDB installed. The production has MySQL 8 installed. When I transfer new schema changes from MariaDB to MySQL, it works mostly. I have to fiddle sometimes. But my main problem is when…
Rohit Gupta
  • 2,116
  • 8
  • 19
  • 25
2
votes
1 answer

Adding data to a DB as migrations

Some Background We currently have a PostgreSQL DB that is "read-only", i.e. no user is allowed to write to it and all insertions are done in a controlled manner. For a while, these were done by manually adding this data or creating a copy of the DB…
fditz
  • 121
  • 3
2
votes
3 answers

Is it good practice to refer to a table with different names in a database schema?

We have a database currently running with a table named a certain way (let's say thing for the sake of example), and references to that table named the same way (FK would be named thing_id for instance). We also used to have this thing concept in…
Aweb
  • 21
  • 2
2
votes
0 answers

Received an unsupported type from Postgres. (14) (SQLGetData)

I have database in PostgreSQL. It is simplest possible database with schema presented here: http://www.postgresqltutorial.com/postgresql-create-table/ I want to migrate it to MySQL. I trying to follow after…
Daniel
  • 209
  • 1
  • 3
  • 10
1
vote
1 answer

T-SQL schema changes that can be performed in O(1)

We want to minimize the downtime for our users during the upgrade process and want to ensure that our schema changes can all be performed in O(1) and are not O(n). Is there a list of documented schema changes that can be performed in O(1) with…
D.R.
  • 343
  • 2
  • 8
1
vote
1 answer

Migrate MyISAM tables to InnoDB using replication to avoid a long down time

We have to migrate MyISAM tables to InnoDB but we are afread of a long down time. In our database we have 10 MyISAM tables where 2 of them are the biggest: ~26GB data + ~10GB indexes. My question is: Can I set replication between primary server…
Ela
  • 500
  • 4
  • 9
1
vote
1 answer

Error: Not run migration 1604169742656_add-users-table is preceding already run migration 1604024614269_table-comments

I was trying to run a migration file to create a table of users but I kept getting the following error: Error: Not run migration 1604169742656_add-users-table is preceding already run migration 1604024614269_table-comments I worked around it by…
Daniel
  • 281
  • 2
  • 3
  • 15
0
votes
0 answers

Is this a good definition of schema migration?

I'm trying to preserve the meaning of the English word 'migration' in the definition, and I think I might be able to do that without making up a fake definition. Here goes: A schema migration is the change of a database's schema from one state to…
Joel
  • 1
0
votes
1 answer

The SELECT permission was denied on the object '__EFMigrationsHistory', database 'master', schema 'dbo'

I'm trying to create asp.net core web api. (https://youtu.be/EmV_IBYIlyo?si=GcPVN7BuautDy6-j&t=1351) At the point where he executes Add-Migration InitialCreate and Update-database my console spits out error: The SELECT permission was denied on the…
Lukas Čaplikas
  • 1
  • 2
0
votes
0 answers

Horizontal scaling with database schema

I am designing a database schema for our customers on-premise, which will be moved to Azure PaaS later. Regarding the "scale out" strategies I lean towards to create "one database", and create schema for each and every customer to store the database…
Avi
  • 265
  • 2
  • 15
0
votes
2 answers

Error when migrating MySQL 8 to SQL Server 2019

I get an error while migrating a database from MySQL 8.0 to SQL Server 2019 using the SQL Server Migration Assistant. The error message: ExecuteReader requires an open and available Connection. The connection's current state is closed. The same…
Carlos Vaz
  • 11
  • 3
0
votes
1 answer

Achieving best practice schema and permissions management on PostgreSQL

I have a PostgreSQL database (12, moving to 13 imminently) with a single (Rails) application running on it, and no other users or systems connecting. The application uses the postgres user to connect to the database for all purposes - regular…
David Aldridge
  • 2,198
  • 11
  • 14
1
2