Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tiJnu-00EQWw-31 for pgsql-general@arkaria.postgresql.org; Wed, 12 Feb 2025 20:57:54 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tiJns-00EFPx-4S for pgsql-general@arkaria.postgresql.org; Wed, 12 Feb 2025 20:57:52 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tiJnr-00EFPf-QV for pgsql-general@lists.postgresql.org; Wed, 12 Feb 2025 20:57:52 +0000 Received: from mail-il1-x144.google.com ([2607:f8b0:4864:20::144]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tiJnq-000Uja-1C for pgsql-general@lists.postgresql.org; Wed, 12 Feb 2025 20:57:52 +0000 Received: by mail-il1-x144.google.com with SMTP id e9e14a558f8ab-3d03d2bd7d2so1065875ab.0 for ; Wed, 12 Feb 2025 12:57:50 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1739393868; x=1739998668; darn=lists.postgresql.org; h=to:subject:message-id:date:from:mime-version:from:to:cc:subject :date:message-id:reply-to; bh=ROspeYgqN8LPTlwDg4uf7LygVlNOzr4Gi9pHIImaCoI=; b=ITDdpMyyprKvg2AxE+FMgBsswrrHtoyUC0JQmgk9f4BobiTkrTcV0ly9ogEr9LHn7U CkGSc2iI5su2rOX/msQkTY4bGXvUmixSNFcYCnkggrDF/kzhNQNo2v+j8NUhxKSLyWaE zSv7g6J3U4aGvG5a33/+/v/g0H76vUZTIJc44Mw6NYqyOek434PlzfwbI0GTF1xxB6TC 6sxLcsgTD0pcO8VgpQi6dJqXTj52JVhWbW09tq3q3BlUYSHnGTSKtk0tuumOMGNbfz3+ odeK3X5ugtGn4BqQDkcxSW43xiSXZ6ZeOl5csRJ0LAWP47uMiS4MHw13qP5WnairjF2M bUow== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1739393868; x=1739998668; h=to:subject:message-id:date:from:mime-version:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=ROspeYgqN8LPTlwDg4uf7LygVlNOzr4Gi9pHIImaCoI=; b=K1icRhgi7boVuo1ZuC4Njd7qrTj13mvUN8RJwxusWMf0zRb1EVeub49hFC2NsUGwVl NAt8zdoDEyPaRJP6pR8qhvDfp2PflEVAMOJMRRq3zvw/0ps962+0A8TfV4ix4aoj7IHA CoLWkaeR6bmWGPktecylrTOwrYq1NrLkKSWFBJIFRPWXcItirXaDI0pKttxh4IPjB4Wa rOu+6CgaMO5il1jBIwZ8gFgZCjUUcVtNsRbz7bYVdCcIjfy3EeAAGzjTtXEH9bsY3mUi KtHI7GEc54rf2HlVQs/w5ojeGN5PISHUvLI+D4qyxGcbCWl84CTGVP7xUqkXf9wHr77c 6sMQ== X-Gm-Message-State: AOJu0YwCIu4ZBVRTkS+Rh+4Rwc0eZ5Q7zRUy9f8WRReA1TIW4LVCp+UG BJCx0bMZEE5uYqq9KhWrT2rPGiUDP01zxQgr7Qpfp773/RjeGW3uFNe8kvdoq31fqk+T5EKkjfV 6HrKnEOUvrrTG9se6vd5rCYmTLGoB43JFs98= X-Gm-Gg: ASbGncuYC/J5uGAGE3TtqcdVJbn4TjbEekDpQfvZfP94zh0q3clXfTjeL1p9QLXqrb9 Meg0JEc6w/ekq5yFN82Jp/pbwfDdP8xupgIhjUOEdSrNWsdpKQ2rNa+r3Waw2PF/SBhjVzCPmSw == X-Google-Smtp-Source: AGHT+IFVybNcCNwhqpCL8dsflhj7HOARjLinEi62qxdy5w6EvOe/43xJRvikbbZ3A/g8rKGF35Fevv/MfKJ9e++o9Is= X-Received: by 2002:a05:6e02:12c2:b0:3d0:25d0:8507 with SMTP id e9e14a558f8ab-3d18c22b0fbmr8601445ab.6.1739393868337; Wed, 12 Feb 2025 12:57:48 -0800 (PST) MIME-Version: 1.0 From: Marcelo Fernandes Date: Thu, 13 Feb 2025 09:57:36 +1300 X-Gm-Features: AWEUYZlqOluRUunN2itPhZMkaU9mCeg_girsep2iSVQeBdnnR2CJ-4q0ohXqvF4 Message-ID: Subject: Best Approach for Swapping a Table with its Copy To: pgsql-general@lists.postgresql.org Content-Type: text/plain; charset="UTF-8" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Hi folks, I have a scenario where I need to swap an original table with a copy of that table. The copy has an exclusion constraint that the original does not have. The main challenge is to ensure that the swap does not break any existing foreign keys to the original table and handles the associated TOAST tables correctly. Both tables are fairly large and exist in a system where there are no maintenance time windows where the application is shut down. My key questions are: - What is the best process for swapping the original table with the copy in such a way that the foreign key relations are preserved? - Are there any special considerations for managing the TOAST tables during this swap? - Should I perform this operation in multiple steps, or is there a straightforward way to achieve this atomically? - Are there any risks of potential issues I should be aware of when doing this swap? Specifically related to foreign key integrity and TOAST data? Thank you! - Marcelo