New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Support for Postgresql out of the box #1333

Closed
kretz opened this Issue Oct 29, 2013 · 29 comments

Comments

Projects
None yet
@kretz

kretz commented Oct 29, 2013

It would be great if ghost would support a Postgresql database out of the box, i.e. adding the 'pg' module an an (optional) dependency so just configuring the config.js would be enough.

Currently, changing package.json file is tedious and error prone between releases for us Postgres users. Also, adding the pg module is not trivial in a chef/puppet/saltstack setup.

Is adding pg default something you would consider? SQLite3 and MySQL is already there.

Thanks,
Martin

@ErisDS

This comment has been minimized.

Show comment
Hide comment
@ErisDS

ErisDS Oct 29, 2013

Member

Supporting pg out of the box means we need a way to test that it is working. As yet no one on the core Ghost team has had time to setup pg and verify that Ghost does work as expected, including running the tests against it.
Also, we still have an open issue to run the tests against MySQL by default, we'd also need to add pg to that.

Additionally, there is a bug fix on master for postgres, if there are more needed I'd like to get them raised.

In short, official pg support for 0.4 is certainly something we can consider, it just requires a little work.

Member

ErisDS commented Oct 29, 2013

Supporting pg out of the box means we need a way to test that it is working. As yet no one on the core Ghost team has had time to setup pg and verify that Ghost does work as expected, including running the tests against it.
Also, we still have an open issue to run the tests against MySQL by default, we'd also need to add pg to that.

Additionally, there is a bug fix on master for postgres, if there are more needed I'd like to get them raised.

In short, official pg support for 0.4 is certainly something we can consider, it just requires a little work.

@jgillich

This comment has been minimized.

Show comment
Hide comment
@jgillich

jgillich Oct 29, 2013

Member

Ghost appears to work fine (created a post and changed a few settings, no problem), but the tests don't:

[jakob@thinkpad Ghost]$ grunt test-functional
Running "clean:test" (clean) task

Running "setTestEnv" task

Running "express:test" (express) task
Starting background Express server
Stopping Express server
[jakob@thinkpad Ghost]$ grunt test-unit
Running "clean:test" (clean) task

Running "setTestEnv" task

Running "loadConfig" task

No error, they just don't run. All of them pass when using sqlite so I don't think it's related to my setup.

Ghost master (8a8dacb)
Fedora 19 x64, Postgresql 9.2.5, pg 2.8.2

(Deleted previous comment because I didn't actually run the tests against pg)

Member

jgillich commented Oct 29, 2013

Ghost appears to work fine (created a post and changed a few settings, no problem), but the tests don't:

[jakob@thinkpad Ghost]$ grunt test-functional
Running "clean:test" (clean) task

Running "setTestEnv" task

Running "express:test" (express) task
Starting background Express server
Stopping Express server
[jakob@thinkpad Ghost]$ grunt test-unit
Running "clean:test" (clean) task

Running "setTestEnv" task

Running "loadConfig" task

No error, they just don't run. All of them pass when using sqlite so I don't think it's related to my setup.

Ghost master (8a8dacb)
Fedora 19 x64, Postgresql 9.2.5, pg 2.8.2

(Deleted previous comment because I didn't actually run the tests against pg)

@halfdan

This comment has been minimized.

Show comment
Hide comment
@halfdan

halfdan Nov 2, 2013

Member

@jgillich Tests run fine for me - also on travis. I discussed with @ErisDS on IRC and it might be best to wait for #306 to be implemented before taking Ghost to another database. Ghost seems to run fine on PostgreSQL for now.

Member

halfdan commented Nov 2, 2013

@jgillich Tests run fine for me - also on travis. I discussed with @ErisDS on IRC and it might be best to wait for #306 to be implemented before taking Ghost to another database. Ghost seems to run fine on PostgreSQL for now.

@ErisDS

This comment has been minimized.

Show comment
Hide comment
@ErisDS

ErisDS Nov 2, 2013

Member

This is a valid issue, and we certainly want to add official and proper postgres support at some point in the future. This just isn't on the roadmap right now, and there are other high priority things which are and which would be made much harder if we officially support all 3.

If people find bugs and want to submit fixes we're more than happy to accept them.

Member

ErisDS commented Nov 2, 2013

This is a valid issue, and we certainly want to add official and proper postgres support at some point in the future. This just isn't on the roadmap right now, and there are other high priority things which are and which would be made much harder if we officially support all 3.

If people find bugs and want to submit fixes we're more than happy to accept them.

@willglynn

This comment has been minimized.

Show comment
Hide comment
@willglynn

willglynn Nov 2, 2013

Contributor

which would be made much harder if we officially support all 3

Really? It seems to me like it's much harder to bolt on PostgreSQL support later than it would be to keep it working as features come in, especially as Travis supports each of the data stores in question and already evaluates every pull request.

I don't object in principle to PostgreSQL support coming later, but what that usually means is that PostgreSQL users become second-class citizens, where the core app works but the rest of the ecosystem doesn't. (See almost every PHP app.) Supporting it now prevents this.

Contributor

willglynn commented Nov 2, 2013

which would be made much harder if we officially support all 3

Really? It seems to me like it's much harder to bolt on PostgreSQL support later than it would be to keep it working as features come in, especially as Travis supports each of the data stores in question and already evaluates every pull request.

I don't object in principle to PostgreSQL support coming later, but what that usually means is that PostgreSQL users become second-class citizens, where the core app works but the rest of the ecosystem doesn't. (See almost every PHP app.) Supporting it now prevents this.

@kretz

This comment has been minimized.

Show comment
Hide comment
@kretz

kretz Nov 2, 2013

I agree with @willglynn.

Also, enabling pg in package.json already now will make more people use it, and as a result more bugs will be reported. And hopefully patches will be created.

If you are worried that enabling pg by default now will reflect badly on ghost, then perhaps marking it as "unofficial" support will avoid that?

kretz commented Nov 2, 2013

I agree with @willglynn.

Also, enabling pg in package.json already now will make more people use it, and as a result more bugs will be reported. And hopefully patches will be created.

If you are worried that enabling pg by default now will reflect badly on ghost, then perhaps marking it as "unofficial" support will avoid that?

@ErisDS

This comment has been minimized.

Show comment
Hide comment
@ErisDS

ErisDS Nov 2, 2013

Member

@willglynn Although Travis supports each of the data stores, at the moment our test suite is still not complete enough to rely upon.

Due to this, adding pg support means more manual testing work for the core team, and that is not something I'm willing to commit the team to at this point in time when we are busy focusing on so many other things (see the Roadmap).

I certainly don't want to make pg users second class citizens, but another way of looking at it is that currently they are 3rd behind sqlite3 and mysql. We need to learn a little bit more about leveling the playing field between these two, and if we do that right, adding pg support should be possible without increasing the workload.

To do that we need to ensure that we have a more complete set of unit and integration tests (see #1189, #361, etc), and make sure we aren't doing things like #1385 (thanks for that). We also need to get a better grip on things like migrations, how to extend bookshelf for things like search, and other bits of learning that once we have in our toolkit we'll be in a much better position. This is still a very, very young project and I do not want to run before we can walk.

Member

ErisDS commented Nov 2, 2013

@willglynn Although Travis supports each of the data stores, at the moment our test suite is still not complete enough to rely upon.

Due to this, adding pg support means more manual testing work for the core team, and that is not something I'm willing to commit the team to at this point in time when we are busy focusing on so many other things (see the Roadmap).

I certainly don't want to make pg users second class citizens, but another way of looking at it is that currently they are 3rd behind sqlite3 and mysql. We need to learn a little bit more about leveling the playing field between these two, and if we do that right, adding pg support should be possible without increasing the workload.

To do that we need to ensure that we have a more complete set of unit and integration tests (see #1189, #361, etc), and make sure we aren't doing things like #1385 (thanks for that). We also need to get a better grip on things like migrations, how to extend bookshelf for things like search, and other bits of learning that once we have in our toolkit we'll be in a much better position. This is still a very, very young project and I do not want to run before we can walk.

@willglynn

This comment has been minimized.

Show comment
Hide comment
@willglynn

willglynn Nov 3, 2013

Contributor

All right – it sounds like we're not far from being on the same page. The issue is core team workload, not the intention of fully supporting PostgreSQL out of the box. To that end, I have two other suggestions:

  • Would it be all right to have Travis run against PostgreSQL if it were marked as an allowed failure?

    The benefit there is that interested parties (like me!) can see what parts of the test suite are failing on PostgreSQL without affecting the red/green status each build. It also lets the Ghost team evaluate PostgreSQL-related pull requests without requiring changes to their local development environments.

  • Could we get PostgreSQL mentioned along with SQLite and MySQL in future developer-facing documentation? In particular, I don't want PostgreSQL to be a surprise for plugin authors when it happens later; I'd rather people build on the understanding that it's intended to be a supported deployment environment.

Contributor

willglynn commented Nov 3, 2013

All right – it sounds like we're not far from being on the same page. The issue is core team workload, not the intention of fully supporting PostgreSQL out of the box. To that end, I have two other suggestions:

  • Would it be all right to have Travis run against PostgreSQL if it were marked as an allowed failure?

    The benefit there is that interested parties (like me!) can see what parts of the test suite are failing on PostgreSQL without affecting the red/green status each build. It also lets the Ghost team evaluate PostgreSQL-related pull requests without requiring changes to their local development environments.

  • Could we get PostgreSQL mentioned along with SQLite and MySQL in future developer-facing documentation? In particular, I don't want PostgreSQL to be a surprise for plugin authors when it happens later; I'd rather people build on the understanding that it's intended to be a supported deployment environment.

@halfdan

This comment has been minimized.

Show comment
Hide comment
@halfdan

halfdan Nov 3, 2013

Member

@willglynn @ErisDS I like the idea of having pg configured as an allowed failure - this will show us early on if a feature we're building needs to be adjusted to pg at some point.

Member

halfdan commented Nov 3, 2013

@willglynn @ErisDS I like the idea of having pg configured as an allowed failure - this will show us early on if a feature we're building needs to be adjusted to pg at some point.

@sjama

This comment has been minimized.

Show comment
Hide comment
@sjama

sjama Nov 3, 2013

Contributor

@ErisDS I don't if you had a chance to look that alternative approach I sent you but one of my main points was the suggestion to take all database related tasks out of core and into a separate module.

Contributor

sjama commented Nov 3, 2013

@ErisDS I don't if you had a chance to look that alternative approach I sent you but one of my main points was the suggestion to take all database related tasks out of core and into a separate module.

@ErisDS

This comment has been minimized.

Show comment
Hide comment
@ErisDS

ErisDS Nov 14, 2013

Member

I like the idea of configuring PostGres as a permitted failure.
I'd also be really happy for someone to do a PR for the pg specific stuff - there is at least 1 place where we have code specifically for sqlite3 and mysql - need to either add pg or move the functionality (getTables) back onto knex.

Member

ErisDS commented Nov 14, 2013

I like the idea of configuring PostGres as a permitted failure.
I'd also be really happy for someone to do a PR for the pg specific stuff - there is at least 1 place where we have code specifically for sqlite3 and mysql - need to either add pg or move the functionality (getTables) back onto knex.

@willglynn

This comment has been minimized.

Show comment
Hide comment
@willglynn

willglynn Nov 14, 2013

Contributor

SQL-92 specified a standard way of getting a list of tables (select table_name from information_schema.tables) supported by MySQL and PostgreSQL, along with MSSQL and other databases not currently under discussion.

Unfortunately, SQLite 3 does not provide information_schema, but given that it's part of the ANSI SQL standard, I'd argue it makes sense to implement getTables() to use it and cover SQLite 3 as a special case.

Contributor

willglynn commented Nov 14, 2013

SQL-92 specified a standard way of getting a list of tables (select table_name from information_schema.tables) supported by MySQL and PostgreSQL, along with MSSQL and other databases not currently under discussion.

Unfortunately, SQLite 3 does not provide information_schema, but given that it's part of the ANSI SQL standard, I'd argue it makes sense to implement getTables() to use it and cover SQLite 3 as a special case.

halfdan added a commit to halfdan/Ghost that referenced this issue Nov 25, 2013

halfdan added a commit to halfdan/Ghost that referenced this issue Nov 26, 2013

halfdan added a commit to halfdan/Ghost that referenced this issue Nov 29, 2013

Remove direct pg dependency
refs TryGhost#1333
- Adjust Travis build to install pg
- Remove pg from package.json
@yanivtal

This comment has been minimized.

Show comment
Hide comment
@yanivtal

yanivtal Jan 2, 2014

+1. Thanks for adding postgres to Travis.

yanivtal commented Jan 2, 2014

+1. Thanks for adding postgres to Travis.

@trisweb

This comment has been minimized.

Show comment
Hide comment
@trisweb

trisweb Feb 19, 2014

What's the status of this? Any specific areas that need work or fixing other than the failing test cases?

trisweb commented Feb 19, 2014

What's the status of this? Any specific areas that need work or fixing other than the failing test cases?

@ErisDS

This comment has been minimized.

Show comment
Hide comment
@ErisDS

ErisDS Feb 19, 2014

Member

@trisweb I've been thinking about this - the main factor involved is the additional manual testing work, and the fact that few people working on Ghost have postgres setup locally. The team is still pretty small, and I'm loathe to force extra work on them right now if I can avoid it.

Other than things like #2075 being fixed, it would really help if we could get the following:

  • Postgres added to Ghost-Vagrant (I just opened an issue. Ghost-Vagrant needs a bit of love, but I think adding pg & mysql, and perhaps coming up with a way to keep a customised config.js on there would really help.
  • A how-to wiki page covering setting up postgres for development on mac/win/lin

These are boring admin tasks, but the way to get pg officially supported is all about facilitating the development process to include it :)

Member

ErisDS commented Feb 19, 2014

@trisweb I've been thinking about this - the main factor involved is the additional manual testing work, and the fact that few people working on Ghost have postgres setup locally. The team is still pretty small, and I'm loathe to force extra work on them right now if I can avoid it.

Other than things like #2075 being fixed, it would really help if we could get the following:

  • Postgres added to Ghost-Vagrant (I just opened an issue. Ghost-Vagrant needs a bit of love, but I think adding pg & mysql, and perhaps coming up with a way to keep a customised config.js on there would really help.
  • A how-to wiki page covering setting up postgres for development on mac/win/lin

These are boring admin tasks, but the way to get pg officially supported is all about facilitating the development process to include it :)

@trisweb

This comment has been minimized.

Show comment
Hide comment
@trisweb

trisweb Feb 19, 2014

Great, thank you. Please let me know if there's any way I can help. I plan to use ghost for my site and would love to run it on postgres, and can set up a dev environment and contribute back to aid the process. I'd also be happy to work on docs.

trisweb commented Feb 19, 2014

Great, thank you. Please let me know if there's any way I can help. I plan to use ghost for my site and would love to run it on postgres, and can set up a dev environment and contribute back to aid the process. I'd also be happy to work on docs.

@ErisDS

This comment has been minimized.

Show comment
Hide comment
@ErisDS

ErisDS Feb 19, 2014

Member

Please feel free to jump in :) The Ghost wiki is completely open, so you can add docs there easily (although there are no notifications, so please do drop us a link here if you do).

Ghost-Vagrant is also open source, and the bug #2075 just needs someone to love it ;) Perhaps you know other developers who work with pg who you could encourage to get involved as well?

Member

ErisDS commented Feb 19, 2014

Please feel free to jump in :) The Ghost wiki is completely open, so you can add docs there easily (although there are no notifications, so please do drop us a link here if you do).

Ghost-Vagrant is also open source, and the bug #2075 just needs someone to love it ;) Perhaps you know other developers who work with pg who you could encourage to get involved as well?

@trisweb

This comment has been minimized.

Show comment
Hide comment
@trisweb

trisweb Feb 19, 2014

Great, thanks. I'll have at it then! Anyone else watching this interested in combining efforts?

trisweb commented Feb 19, 2014

Great, thanks. I'll have at it then! Anyone else watching this interested in combining efforts?

@ErisDS

This comment has been minimized.

Show comment
Hide comment
@ErisDS

ErisDS Jul 29, 2014

Member

In the interest of transparency, it's worth noting that the postgres support has significantly degraded in 0.5. The build fails very early on, so I think we'll probably release 0.5 with a no pg support warning.

At present, we don't have any contributors championing efforts to make it easy for everyone to test, working on keeping the build working or improving support & docs for pg. We really need someone who is using pg in production to drive this effort if we are ever going to officially support postgres.

Member

ErisDS commented Jul 29, 2014

In the interest of transparency, it's worth noting that the postgres support has significantly degraded in 0.5. The build fails very early on, so I think we'll probably release 0.5 with a no pg support warning.

At present, we don't have any contributors championing efforts to make it easy for everyone to test, working on keeping the build working or improving support & docs for pg. We really need someone who is using pg in production to drive this effort if we are ever going to officially support postgres.

@kellyjandrews

This comment has been minimized.

Show comment
Hide comment
@kellyjandrews

kellyjandrews Aug 13, 2014

I may be able to assist here. We have been using pg on heroku on the 0.4.x release. So the main ask are the "boring admin tasks" listed above? What else will help get over the pg hurdles in 0.5?

kellyjandrews commented Aug 13, 2014

I may be able to assist here. We have been using pg on heroku on the 0.4.x release. So the main ask are the "boring admin tasks" listed above? What else will help get over the pg hurdles in 0.5?

@kellyjandrews

This comment has been minimized.

Show comment
Hide comment
@kellyjandrews

kellyjandrews Aug 13, 2014

I'll most likely pull from here - http://www.postgresqltutorial.com/ and I'm working on setting up a testing environment with ghost-vagrant now. This could be fun :)

kellyjandrews commented Aug 13, 2014

I'll most likely pull from here - http://www.postgresqltutorial.com/ and I'm working on setting up a testing environment with ghost-vagrant now. This could be fun :)

@kellyjandrews

This comment has been minimized.

Show comment
Hide comment
@kellyjandrews

kellyjandrews Aug 13, 2014

@trisweb Have you had any progress? Looking to help out as well. How far have you gotten?

kellyjandrews commented Aug 13, 2014

@trisweb Have you had any progress? Looking to help out as well. How far have you gotten?

@trisweb

This comment has been minimized.

Show comment
Hide comment
@trisweb

trisweb Aug 13, 2014

I gave up the effort some time ago. At this time I don't have capacity to help.

If it were me, I'd just point it to postgres and begin debugging tests and visible issues and go from there.

trisweb commented Aug 13, 2014

I gave up the effort some time ago. At this time I don't have capacity to help.

If it were me, I'd just point it to postgres and begin debugging tests and visible issues and go from there.

@ErisDS

This comment has been minimized.

Show comment
Hide comment
@ErisDS

ErisDS Aug 14, 2014

Member

@kellyjandrews Would be great to get someone working on this. To begin it is certainly a case of doing the admin tasks to make it easy for the team to test and dev with postgres.

Next we need to get the build passing reliably with postgres. This means filling the gaps where postgres isn't working 100% in Ghost, and there is some difficulty here I believe due to postgres handling transactions quite differently to sqlite3 and mysql. @halfdan knows more about this.

Member

ErisDS commented Aug 14, 2014

@kellyjandrews Would be great to get someone working on this. To begin it is certainly a case of doing the admin tasks to make it easy for the team to test and dev with postgres.

Next we need to get the build passing reliably with postgres. This means filling the gaps where postgres isn't working 100% in Ghost, and there is some difficulty here I believe due to postgres handling transactions quite differently to sqlite3 and mysql. @halfdan knows more about this.

@ErisDS

This comment has been minimized.

Show comment
Hide comment
@ErisDS

ErisDS Aug 20, 2014

Member

resolving #3825 would be great to improve pg support.

Member

ErisDS commented Aug 20, 2014

resolving #3825 would be great to improve pg support.

@ErisDS ErisDS modified the milestones: 0.5.x Backlog, Future Backlog Sep 2, 2014

@ErisDS ErisDS added the server label Sep 2, 2014

morficus pushed a commit to morficus/Ghost that referenced this issue Sep 4, 2014

morficus pushed a commit to morficus/Ghost that referenced this issue Sep 4, 2014

morficus pushed a commit to morficus/Ghost that referenced this issue Sep 4, 2014

Remove direct pg dependency
refs TryGhost#1333
- Adjust Travis build to install pg
- Remove pg from package.json
@jaswilli

This comment has been minimized.

Show comment
Hide comment
@jaswilli

jaswilli Nov 1, 2014

Member

Issues #2075 and #3825 have been closed, and I have a PR (TryGhost/Ghost-Vagrant#50) open on Ghost-Vagrant to set up PostgreSQL during provisioning so that it's easily available for developers to use.

The pg build has been passing reliably on Travis for quite some time now--are we ready to treat it as a first-class citizen along with MySQL and sqlite3? My vote is yes.

Member

jaswilli commented Nov 1, 2014

Issues #2075 and #3825 have been closed, and I have a PR (TryGhost/Ghost-Vagrant#50) open on Ghost-Vagrant to set up PostgreSQL during provisioning so that it's easily available for developers to use.

The pg build has been passing reliably on Travis for quite some time now--are we ready to treat it as a first-class citizen along with MySQL and sqlite3? My vote is yes.

@ErisDS

This comment has been minimized.

Show comment
Hide comment
@ErisDS

ErisDS Nov 1, 2014

Member

My concerns around supporting it have always largely come from the fact that in older times the responsibility would fall to me, and I don't have any experience with it. Given we now have core team members who are comfortable with supporting it then I see no reason not to.

Member

ErisDS commented Nov 1, 2014

My concerns around supporting it have always largely come from the fact that in older times the responsibility would fall to me, and I don't have any experience with it. Given we now have core team members who are comfortable with supporting it then I see no reason not to.

jaswilli added a commit to jaswilli/Ghost that referenced this issue Nov 4, 2014

Add pg as an optional dependency.
Closes TryGhost#1333
- Change PostgreSQL build on Travis from an allowed failure to
  failing the tests just like sqlite3 and MySQL.

@ErisDS ErisDS closed this in #4395 Nov 8, 2014

@jloh

This comment has been minimized.

Show comment
Hide comment
@jloh

jloh Aug 4, 2015

Sorry to bump an old thread, but http://support.ghost.org/config/#database says that Postgres is still not officially supported?

jloh commented Aug 4, 2015

Sorry to bump an old thread, but http://support.ghost.org/config/#database says that Postgres is still not officially supported?

@ErisDS

This comment has been minimized.

Show comment
Hide comment
@ErisDS

ErisDS Aug 6, 2015

Member

@MooASh good catch, I'm actually loathe to update that as postgres doesn't get the same love during development that sqlite3 and MySQL get. The level of support isn't the same, and I would even go so far as to say it may get dropped again, or miss out on major features in future (e.g. if we added FTS).

Member

ErisDS commented Aug 6, 2015

@MooASh good catch, I'm actually loathe to update that as postgres doesn't get the same love during development that sqlite3 and MySQL get. The level of support isn't the same, and I would even go so far as to say it may get dropped again, or miss out on major features in future (e.g. if we added FTS).

kirrg001 added a commit that referenced this issue Sep 16, 2016

kirrg001 added a commit that referenced this issue Sep 19, 2016

ErisDS added a commit that referenced this issue Sep 20, 2016

mixonic added a commit to mixonic/Ghost that referenced this issue Oct 28, 2016

madfrog2047 added a commit to madfrog2047/Ghost that referenced this issue Nov 20, 2016

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment