Скачать или смотреть How to Increment All Sequences in PostgreSQL During Migration

Learn how to successfully increment all sequences in PostgreSQL when migrating databases, and understand why some sequences may be overlooked in your queries.
---
This video is based on the question https://stackoverflow.com/q/76058199/ asked by the user 'P_Ar' ( https://stackoverflow.com/u/6346663/ ) and on the answer https://stackoverflow.com/a/76060160/ provided by the user 'Laurenz Albe' ( https://stackoverflow.com/u/6464308/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Increment all sequences in Postgresql DB

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Increment All Sequences in PostgreSQL During Migration

When migrating a PostgreSQL database, especially from Aurora Postgres version 10 to version 12, maintaining the integrity of sequences can become a significant challenge. If you're using AWS Database Migration Service (DMS), you may discover that sequences are not synchronized during the migration process, leading to potential issues with primary keys and data consistency. In this guide, we'll walk through how to effectively increment all sequences in your PostgreSQL database, ensuring a smooth migration to your target environment.

The Problem: Missing Sequences During Migration

As you prepare for your database migration, one critical aspect to consider is the management of sequences. A user encountered an issue where a SELECT query designed to retrieve sequences from the database returned fewer results than expected. Specifically, while the database displayed 25 sequences using the command \ds public.*, the query only retrieved 15 sequences. This discrepancy could potentially lead to issues in data integrity if not addressed properly.

Let’s examine the SQL query that was utilized to retrieve the sequences:

[[See Video to Reveal this Text or Code Snippet]]

The Solution: Understanding Sequence Relationships

Upon closer inspection, it was identified that the query was only returning sequences that were directly related to a specific table column. Sequences can often be owned by table columns in PostgreSQL, which establishes the connection that the query relied on to fetch results.

Key Takeaways:

Ownership Relationship: Your query specifically targets sequences owned by table columns. For instance, a sequence can be associated with a column through an ALTER SEQUENCE command, such as:

[[See Video to Reveal this Text or Code Snippet]]

Unrelated Sequences: Any sequences that are not tied to a specific table column will likely be omitted from the results of the original query. This could explain why the query returned only 15 sequences instead of the full 25 present in the database.

How to Retrieve All Sequences

To ensure you retrieve all sequences, regardless of their relationship to table columns, you can adjust your query. You might consider using a simpler query that queries the pg_class table directly for all sequences:

[[See Video to Reveal this Text or Code Snippet]]

This approach directly retrieves all sequences in the defined schema, giving you a complete overview without the restrictions based on ownership.

Conclusion

Migrating a PostgreSQL database involves careful consideration of all database objects, including sequences. If you've run into issues with missing sequences, remember that the sequences must be tied to specific table columns to be included in your primary query. By broadening your approach and leveraging queries that encompass all sequences, you can facilitate a much smoother migration process.

If you have questions or need further assistance with your PostgreSQL migration, feel free to reach out!