Thread: Loading test data into a PostgreSQL database
Hi, I'm currently working on a project using PostgreSQL 13 and using an extension with CREATE EXTENSION to create the database.However, if I want to make certain changes to the database, I have to drop the extension and then re-create it (Idon't want to update the version number for the initial release). So, what is the normal way of creating some test datato put in the database because obviously, when you do DROP EXTENSION, it deletes everything? Can I make a CSV file and import it into the database? Or do I have to write some custom SQL to handle that aspect? Or isthere an easier method I should be using? Or could I add some test data to the extension itself? Simon.
Attachment
Fwiw I load a pg_dump- (or programmatically)-produced .sql file that creates and loads my table. Bill On 5/14/21 12:11 PM, Simon Connah wrote: > Hi, > > I'm currently working on a project using PostgreSQL 13 and using an extension with CREATE EXTENSION to create the database.However, if I want to make certain changes to the database, I have to drop the extension and then re-create it (Idon't want to update the version number for the initial release). So, what is the normal way of creating some test datato put in the database because obviously, when you do DROP EXTENSION, it deletes everything? > > Can I make a CSV file and import it into the database? Or do I have to write some custom SQL to handle that aspect? Oris there an easier method I should be using? Or could I add some test data to the extension itself? > > Simon. > -- Phobrain.com
I'm currently working on a project using PostgreSQL 13 and using an extension with CREATE EXTENSION to create the database. However, if I want to make certain changes to the database, I have to drop the extension and then re-create it (I don't want to update the version number for the initial release). So, what is the normal way of creating some test data to put in the database because obviously, when you do DROP EXTENSION, it deletes everything?
Will using a template database work? Have your extension and other objects there. When you create your actual database, use the other one as the template.
Regards,
Jayadevan
‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐ On Friday, May 14th, 2021 at 20:27, Bill Ross <ross@cgl.ucsf.edu> wrote: > Fwiw I load a pg_dump- (or programmatically)-produced .sql file that > > creates and loads my table. > > Bill > > On 5/14/21 12:11 PM, Simon Connah wrote: > > > Hi, > > > > I'm currently working on a project using PostgreSQL 13 and using an extension with CREATE EXTENSION to create the database.However, if I want to make certain changes to the database, I have to drop the extension and then re-create it (Idon't want to update the version number for the initial release). So, what is the normal way of creating some test datato put in the database because obviously, when you do DROP EXTENSION, it deletes everything? > > > > Can I make a CSV file and import it into the database? Or do I have to write some custom SQL to handle that aspect? Oris there an easier method I should be using? Or could I add some test data to the extension itself? > > > > Simon. > > -- > > Phobrain.com I tried the pg_dump and all I got for the output was this: https://gist.github.com/simonconnah/ff7d5da342d7a1cab3d05f24f495a89e there was definitely extra content in the database at the time. Things like users and blog posts etc but none of that seemsto have been included in the dump. Having said that I did use JetBrains DataGrip to do the pg_dump so maybe there isa command-line argument I am missing? Simon.
Attachment
‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
On Saturday, May 15th, 2021 at 11:49, Jayadevan M <maymala.jayadevan@gmail.com> wrote:
I'm currently working on a project using PostgreSQL 13 and using an extension with CREATE EXTENSION to create the database. However, if I want to make certain changes to the database, I have to drop the extension and then re-create it (I don't want to update the version number for the initial release). So, what is the normal way of creating some test data to put in the database because obviously, when you do DROP EXTENSION, it deletes everything?Will using a template database work? Have your extension and other objects there. When you create your actual database, use the other one as the template.Regards,Jayadevan
That looks like an interesting thing to look into. I'll have to read through it properly.
Thanks.
Attachment
El 16 de maig de 2021 21.01.04 CEST, Simon Connah <simon.n.connah@protonmail.com> ha escrit: >‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐ > >On Friday, May 14th, 2021 at 20:27, Bill Ross <ross@cgl.ucsf.edu> >wrote: > >> Fwiw I load a pg_dump- (or programmatically)-produced .sql file that >> > >> creates and loads my table. >> > >> Bill >> > >> On 5/14/21 12:11 PM, Simon Connah wrote: >> > >> > Hi, >> > > >> > I'm currently working on a project using PostgreSQL 13 and using an >extension with CREATE EXTENSION to create the database. However, if I >want to make certain changes to the database, I have to drop the >extension and then re-create it (I don't want to update the version >number for the initial release). So, what is the normal way of creating >some test data to put in the database because obviously, when you do >DROP EXTENSION, it deletes everything? >> > > >> > Can I make a CSV file and import it into the database? Or do I have >to write some custom SQL to handle that aspect? Or is there an easier >method I should be using? Or could I add some test data to the >extension itself? >> > > >> > Simon. >> > >> -- >> > >> Phobrain.com > >I tried the pg_dump and all I got for the output was this: > >https://gist.github.com/simonconnah/ff7d5da342d7a1cab3d05f24f495a89e > >there was definitely extra content in the database at the time. Things >like users and blog posts etc but none of that seems to have been >included in the dump. Having said that I did use JetBrains DataGrip to >do the pg_dump so maybe there is a command-line argument I am missing? > >Simon. Hi, Maybe you should try using pg_dumpall, that dumps the whole database cluster, not just one database. -- Pep
‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐ On Sunday, May 16th, 2021 at 21:26, papapep <papapep@gmx.com> wrote: > El 16 de maig de 2021 21.01.04 CEST, Simon Connah simon.n.connah@protonmail.com ha escrit: > > > ‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐ > > > > On Friday, May 14th, 2021 at 20:27, Bill Ross ross@cgl.ucsf.edu > > > > wrote: > > > > > Fwiw I load a pg_dump- (or programmatically)-produced .sql file that > > > > > creates and loads my table. > > > > > Bill > > > > > On 5/14/21 12:11 PM, Simon Connah wrote: > > > > > > Hi, > > > > > > I'm currently working on a project using PostgreSQL 13 and using an > > > > > > > > extension with CREATE EXTENSION to create the database. However, if I > > > > > > > > want to make certain changes to the database, I have to drop the > > > > > > > > extension and then re-create it (I don't want to update the version > > > > > > > > number for the initial release). So, what is the normal way of creating > > > > > > > > some test data to put in the database because obviously, when you do > > > > > > > > DROP EXTENSION, it deletes everything? > > > > > > Can I make a CSV file and import it into the database? Or do I have > > > > > > > > to write some custom SQL to handle that aspect? Or is there an easier > > > > > > > > method I should be using? Or could I add some test data to the > > > > > > > > extension itself? > > > > > > Simon. > > > > > -- > > > > > Phobrain.com > > > > I tried the pg_dump and all I got for the output was this: > > > > https://gist.github.com/simonconnah/ff7d5da342d7a1cab3d05f24f495a89e > > > > there was definitely extra content in the database at the time. Things > > > > like users and blog posts etc but none of that seems to have been > > > > included in the dump. Having said that I did use JetBrains DataGrip to > > > > do the pg_dump so maybe there is a command-line argument I am missing? > > > > Simon. > > Hi, > > Maybe you should try using pg_dumpall, that dumps the whole database cluster, not just one database. > > ------------------------------------------------------------------------------------------------------------ > > Pep Hi, I finally got around to trying this (sorry for the delay) and it just gives me the same output as pg_dump unfortunately. Simon.